SQL Server Selecting Recent Entries

Question

I have a table as shown 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 

Please note that the MyDate format will be YYMMDD, and MyTime will be in a 24-hour format.

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

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

Any help would be greatly appreciated. Thank.

Answer 1

First combine the date + time columns with the date-time, so it’s easy to arrange them. It has been a while since I used Sql Server, but the row_number () function and partitioning is an easy way to find a maximum of one column grouped by another - the section suggestion looks like a group.

 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 

Answer 2

First, you will need to convert the char date to a date field so that you can order it. Then you can do the following:

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

Now remember that MAX (MyDate) and other links to MyDate will not do what you need unless you flip the field into the date field. I suggest creating your own function to convert a char field to a date field.

If you try to sort by MyDate field as is, the results will be sorted alphabetically, as this is a string.

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