CODEWITHSUNDEEP
sqlt-sql
Tug Strongly
Tug Strongly

Reputation: 187

Get the most current datetime of 2 similar records and only display the most current

Any help would be appreciated. Here's my issue... I have a table that have similar records but different update times. e.g -

ID          ComputerName    DomainName  OSType       LastUpdate
12345   PC1111          WORK        Windows7         Mar  5 2014 10:24PM
1723    PC1111          WORK        Windows7         Mar 15 2014  8:30PM
23455       PC1111          WORK        Windows7         Mar 17 2014  8:44PM

The pc names are the same but I can use the IDs and LastUpdate to return just the newest LastUpdate time. I tried MAX(LastUpdate) but no good. I tried TOP 1 and still no good. I am still very new to T-SQL and this is kicking my arse!

Upvotes: 0

Views: 58

Answers (4)

Roland Bouman
Roland Bouman

Reputation: 31991

As a join:

"Find a computer update for which we can find no other update of a computer with the same name and a update time that lies beyond my update"

SELECT    c1.*
FROM      computers c1
LEFT JOIN computers c2
ON        c1.name = c2.name
AND       c1.lastUpdate > c2.lastUpdate
WHERE     c2.id IS NULL

or with a correlated subquery:

"Get the computer update that has the maximum update time of all other updates of computers with the same name."

SELECT    c1.*
FROM      computers c1
WHERE     c1.lastUpdate = (
             SELECT max(c2.lastUpdate)
             FROM   computers c2
             WHERE  c2.name = c1.name
          )

Upvotes: 0

sarin
sarin

Reputation: 5307

It depends on the data type of the column! If LastUpdate is declared as a datetime\smalldatetime etc ordering and selecting the top 1 should work. If it declared as some sort of string then it will do a sort based on the string value which is probably what you are getting.

try

select Max(convert(datetime, LastUpdate, 103))
from Table

or

select Top 1 LastUpdate
from Table
order by convert(datetime, date, 103) ASC

The above two examples convert your string into a date data type. This will mean that SQL Server will know how to sort it when you ask it to!

Upvotes: -1

Tim Schmelter
Tim Schmelter

Reputation: 460340

You could use a cte with row_number:

WITH CTE AS
(
    SELLECT ID, ComputerName, DomainName, OSType, LastUpdate,
       rn = row_number() over (partition by ComputerName order by LastUpdate desc)
    FROM dbo.TableName
)
SELECT ID, ComputerName, DomainName, OSType, LastUpdate
FROM CTE
WHERE RN = 1

Upvotes: 3

M.Ali
M.Ali

Reputation: 69584

SELECT * 
FROM
 (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY ComputerName ORDER BY LastUpdate DESC) AS RN
 FROM Table_NAME
 )Q
WHERE rn = 1

With this query the returned result set will have an extra column RN. just select the column you want by using the column names explicitly in your select statement.

To learn more about the RANKING functions read here SQL SERVER RANKING FUNCTIONS

Upvotes: 1

Related Questions