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 
2

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);
  • 3
    There's no need not to use a subquery/CTE either. This will involve a full sort of all qualifying rows even if an index exists that supports the partition and order by. e.g. as dbfiddle.uk/… – Martin Smith May 29 '18 at 8:34
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
  • Update: Excellent. I have no idea how it works. Because my background is purely in to CRM. However the results come like a charm. Quick question: I'm using this query in a integration tool. Hope there won't be any implications. Thoughts? – L Mudiyanse May 29 '18 at 4:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.