Query in SQL Server (machines that are now in specific project)

[Elina Ma]

I have a table including 3 fields which are Machine Name, Project Name, Date Time where Date Time indicates the exact time when machine was assigned to a project. Now I want to find machines which are now in a specific project (in fact it may be that one machine in different times be allocated to different projects). I want lasted specific project be latest project that machine assigned to it.

[Lost User]

Sounds like a simple query to me. Did I forget something or have I misread the question?

SELECT TOP 1 MachineName
FROM MyTable
WHERE ProjectName = 'Web 8.0'
ORDER BY [DateTime] DESC

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

[Elina Ma]

I want all machines that now be in specific project , may machines "a", 2 days ago in project "1" and now be in project "2" and machines "b" now in project "1", when I wand machines in project "1" ,only "b" should be in result and Should not machine "a" be in result

[Lost User]

Member 8407653 wrote:

I want all machines that now

Change the Sql accordingly. I want coffee.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

[Richard Deeming]

Something like this should work:

WITH cteOrderdAssignments As
(
SELECT
MachineName,
ProjectName,
ROW_NUMBER() OVER ( PARTITION BY MachineName ORDER BY [DateTime] DESC ) As RowNumber
FROM
MyTable
)
SELECT
MachineName
FROM
cteOrderedAssignments
WHERE
RowNumber = 1
And
ProjectName = 'Web 8.0'
ORDER BY
MachineName
;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Herman T Instance]

Row_number() function in combination with common table expression is the best way indeed

In Word you can only store 2 bytes. That is why I use Writer.