SQL Server Choosing the Latest Record for Each Row

Question

I have a SQL Server table as follows:

id(autoincrement) hostname(varchar) group(varchar) member(varchar) datequeried(varchar) 

The table is populated with a scheduled task that scans the network for local members of the Windows PC Administrators group.

• Network scanning - seams with the fact that it may happen that some of the stations are not available during scanning.

The query I would like to write is:

 "select every hostname having the latest datequeried" 

This is a display of the latest result (rows) of each host name requested on the network.

It is clear? I am still encountering some syntax issues, and I am sure it is quite easy.

Thanks in advance.

Answer 1

If you are using SQL SErver 2005 or later (you did not specify ...), you can use CTE to do this:

 ;WITH MostCurrent AS ( SELECT id, hostname, group, member, datequeried, ROW_NUMBER() OVER(PARTITION BY hostname ORDER BY datequeried DESC) 'RowNum' FROM dbo.YourTable ) SELECT * FROM MostCurrent WHERE RowNum = 1 

The internal SELECT inside the CTE "splits" your data into hostname , for example. each hostname receives a new “group” of data, and it enters numbers starting with 1 for each group. These entries are numbered on datequeried DESC , so the most recent one has RowNum = 1 - for each data group (for example, for each hostname ).

Answer 2

From SQL 2005 and later, you can use ROW_NUMBER () as follows:

 ;WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY hostname ORDER BY datequeried DESC) AS RowNo FROM YourTable ) SELECT * FROM CTE WHERE RowNo = 1 

"CTE" is the expression "Commom Table Expression" , basically just an overlay of the first SELECT, which I can then use in the second query.

This will return 1 row for each host name, and the row returned for each of them will be

Answer 3

I can display the required results using:

  select hostname, member, max(lastdatequeried) as lastdatequeried from members group by hostname, member order by hostname 

Thanks to everyone who helped.

Answer 4

 select hostname, max(datequeried) as datequeried from YourTable group by hostname 

Answer 5

 SELECT TOP 1 WITH TIES * FROM YourTable ORDER BY ROW_NUMBER() OVER(PARTITION BY hostname ORDER BY datequeried DESC) 

Answer 6

• Do you want to find every latest scan version of each station?
• Or do you want to find every station that was online (or offline) during the most recent scan?

I would have a major list of workstations in the first place. Then I will have a general list of scans. And then I will have a scan table in which the scan results will be displayed.

To answer # 1, you would use a subquery or inline scan, which returns its id and max (scandal) for each workstation, and then you attach this subquery to the scan table to display the scan string for this identifier of the workstation whose scandal corresponds to its maximum (scandal).

To answer # 2, you should look for all workstations that have a record (or where there is no record, mutatis mutandis) in the scan table, where scandate = max (date) in the list of main scans.