CODEWITHSUNDEEP
sql-serverdateselect
Yassine Souabni
Yassine Souabni

Reputation: 751

SQL Server Selecting newest entry for each row

I have a SQL Server Table like this :

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

The table is filled by a scheduled job that scans network for windows client PCs local admin group members.

The query that I'd like to write is :

"select every hostname having the latest datequeried"

This is to display the newest result (rows) of each hostname queried on network.

Is it clear ? I'm still facing some syntax issues and I'm sure it is quite easy.

Thanks in advance.

Upvotes: 4

Views: 346

Answers (6)

Yassine Souabni
Yassine Souabni

Reputation: 751

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 all who helped.

Upvotes: 1

Tim
Tim

Reputation: 5421

  1. Do you want to find each station's most recent scan?
  2. Or do you want to find every station that was online (or not online) during the most recent scan?

I'd have a master list of workstations, first of all. Then I'd have a master list of scans. And then I'd have the scans table that holds the results of the scans.

To answer #1, you'd would use a subquery or inline view that returns for each workstation its id and max(scandate) and then you'd join that subquery back to scans table to pull out the scan row for that workstation id whose scandate matched its max(scandate).

To answer #2, you'd look for all workstations where exists a record (or where not exists a record, mutatis mutandis) in the scans table where scandate = the max(date) in the master scans list.

Upvotes: 0

Vadim Loboda
Vadim Loboda

Reputation: 3111

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147354

From SQL 2005 and later, you can use ROW_NUMBER() like this:

;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 a Commom Table Expression, basically just aliasing that first SELECT which I can then use in the 2nd query.

This will return 1 row for each hostname, with the row returned for each being the one

Upvotes: 1

marc_s
marc_s

Reputation: 755381

If you're on SQL SErver 2005 or newer (you didn't specify...), you can use a 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 inner SELECT inside the CTE "partitions" your data by hostname, e.g. each hostname gets a new "group" of data, and it numbers those entries starting at 1 for each group. Those entries are numbered by datequeried DESC, so the most recent one has the RowNum = 1 - for each group of data (e.g. for each hostname).

Upvotes: 2

Related Questions