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.

What are the data types of `MyDate` and `MyTime`? And why aren't you using the built-in datatypes for dates and times? — Gordon Linoff, Aug 23 '13 at 01:10
Currently they're int and nchar. However, it doesn't really matter as I imported this data from a csv file and I can define any data type for them. — user2709309, Aug 23 '13 at 01:12
Possible duplicate of [SQL Server: SELECT only the rows with MAX(DATE)](https://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate) — Vadzim, Nov 02 '17 at 18:04

score 13 · Accepted Answer · answered Aug 23 '13 at 01:24

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

This is great if this is the only thing you want to look at. What if I have other joins and fields I want. Where does this fit into that? — dcary, Aug 03 '20 at 18:01

Möoz · Answer 2 · 2017-02-14T19:13:42.823

Firstly, you will need to convert your date char into a date field so that you can order by it. Then what you can do is:

SELECT DISTINCT *
FROM MyTable
WHERE MyTable.MyDate =
(
    SELECT MAX(Sub_MyTable.MyDate)
    FROM MyTable 
    WHERE MyTable.MyKey = Sub_MyTable.MyKey
);

Now remember, the MAX(MyDate) and other references to MyDate won't do what you require unless you turn the field into a date field. I suggest creating your own function to convert the char field into a date field.

If you try to order by the MyDate field as it is, you will have the results sorted in alphabetical order since it's a string.

If the time part is important in the results then you can combine the two as suggested by @jeff.

SQL Server Selecting Records with most recent date time

2 Answers2

Linked