Sql-server – Get related row based on two MAX Values

greatest-n-per-groupmaxsql serversql server 2014

I bumped into a use case where I wanted to write a SQL query to retrieve a row based on 2 MAX values of the table. I tried a couple of combinations but no luck as of yet. Here's a sample table and the requirement:

Index   cardCode    updateDate  updateTS    unitValue
1       sf-001      2018/1/1    1122        $12 
2       sf-001      2018/2/19   7788        $4 
3       sf-002      2018/1/2    2233        $56 
4       sf-003      2018/3/14   3344        $77 
5       sf-003      2018/3/14   4455        $51 
6       sf-003      2018/3/2    6677        $88 
7       sf-004      2018/11/11  8880        $73

The SQL query should return the rows with MAX updateDate per cardCode. E.g. for "sf-001" it should return row 2 (unitValue of $4). If it has two or more identical updateDates then it should check for the MAX updateTS. E.g. for "sf-003" it should return row 5 (unitValue of $51). So the query should result in the following output:

Index   cardCode    updateDate  updateTS    unitValue
1       sf-001      2018/2/19   7788        $4 
2       sf-002      2018/1/2    2233        $56 
3       sf-003      2018/3/14   4455        $51 
4       sf-004      2018/11/11  8880        $73

Best Answer

There is no need for subquery/CTE:

SELECT TOP 1 WITH TIES *
FROM table_name
ORDER BY ROW_NUMBER() OVER(PARTITION BY cardCode 
                           ORDER BY updateDate DESC, updateTS DESC);
Related Question