2

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
Paul White
  • 94,921
  • 30
  • 437
  • 687
L Mudiyanse
  • 123
  • 3

2 Answers2

2

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
Liya Tansky
  • 344
  • 3
  • 14
1

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);
lad2025
  • 364
  • 2
  • 6