Get related row based on two MAX Values 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
This is a clear case to use cte and window functions, please read about them a little. I assume you do not have too much data, so this code is efficient  

;with cte
    as (select Index, cardCode, updateDate, updateTS, unitValue,
        ROW_NUMBER() OVER(PARTITION BY cardCode ORDER BY updateDate DESC, updateTS DESC) as rn
    from YourTable)
    select Index, cardCode, updateDate, updateTS, unitValue
    from cte
    where rn = 1