CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Travis Grannan
Travis Grannan

Reputation: 85

Getting the latest record by DATETIME or NULL

I have the following query:

SELECT DISTINCT 
    c.id, creator, ls.name, ld.timestampOut
FROM 
    countermeasures c
LEFT OUTER JOIN 
    lifecycleDetails ld ON ld.documentId = c.id
LEFT OUTER JOIN 
    lifecycleState ls ON ls.id = ld.lifecycleStatus
WHERE 
    ld.timestampOut = (SELECT MAX(timestampOut) from lifecycleDetails)
    OR ld.timestampOut IS NULL

I'm getting the following results:

enter image description here

I know I'm doing this wrong, but what I'm wanting is the record with the latest date. If timestampOut is null then I'm wanting that record instead. In the end I'm only wanting 1 records not 2.

Thanks!

Upvotes: 0

Views: 79

Answers (2)

RosSQL
RosSQL

Reputation: 323

SELECT DISTINCT top 1 
c.id, creator, ls.name, ld.timestampOut

...

WHERE 
ld.timestampOut = (SELECT MAX(timestampOut) from lifecycleDetails)
OR ld.timestampOut IS NULL

ORDER BY timestampOut

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you are using SQL Server, you can use row_number() for this purpose:

SELECT id, creator, name, timestampOut
FROM (SELECT c.id, creator, ls.name, ld.timestampOut,
             row_number() over (partition by id order by (case when ld.timestampOut is null then 1 else 2 end),
                                                         timestampOut desc
                               ) as seqnum
      FROM countermeasures c
      LEFT OUTER JOIN lifecycleDetails ld ON ld.documentId = c.id
      LEFT OUTER JOIN lifecycleState ls ON ls.id = ld.lifecycleStatus
     ) t
WHERE seqnum = 1;

Upvotes: 2

Related Questions