SQL Server Selecting Records with most recent date time

Question

I have a table as below:

MyJob|   MyKey  |MyCode|MyDate| MyTime 
----------------------------------       
q183b| 0131081a |  24  |100315| 9:37        
q183b| 0131081a |  9   |100315| 11:38        
q183b| 0132426a |  1   |90314 | 15:36        
q183b| 0132426a |  2   |100315| 9:36        
q183b| 0132426a |  85  |100315| 11:36

Note that MyDate format will be YYMMDD and MyTime is in 24 hour format.

I want to return the result of unique MyKey with most recent MyDate and MyTime. The expected result will be something like:

MyJob |   MyKey  |MyCode| MyDate | MyTime        
q183b | 0131081a | 9    | 100315 | 11:38        
q183b | 0132426a | 85   | 100315 | 11:36

Any help will be much appreciated. Thanks.

Answer 1

First combine the date + time columns into a datetime so it's easy to order them. It's been a while since I used Sql Server, but the row_number() function and partitioning is an easy way to find the max of one column grouped by another - the partition clause is similar to a group by.

select t.* 
from
(
    select t.MyKey, t.MyDateTime
    , row_number() over 
         (partition by t.mykey order by t.MyDateTime desc) as keyOrderNbr
    from table t
) A
inner join table t
    on A.MyKey = t.MyKey 
    and A.MyDateTime = t.MyDateTime
where A.keyOrderNbr = 1