Select the greater date if the secondary key is same

Question

I have below tables (table1 & table2). I want to select the ID, Offer ID and Date if it matches a certain Last name. And I want only 2 latest records. Below is my query. This works good, but I want to return only the maximum date or row when OfferID are same. For example, below query is giving me this output Current Output where OfferID are duplicates. I only want one OfferID which was updated/created recently. So the Output I want would be Expected Output. If someone can guide me in the right direction on how would I update my query, I would greatly appreciate it.

SELECT Table1.DATE,
       Table1.OfferID,
       Table2.ID
FROM TABLE1
JOIN TABL2 ON TABLE1.ID = TABLE2.ID
WHERE TABLE2.LASTNAME = "DOE"
ORDER BY Table1.DATE DESC
OFFSET 0 ROW FETCH FIRST 2 ROW ONLY;

Table1

Table2

Please do not upload images of code/data/errors when asking a question. You'll find your experiences here will be much better if you spend some time taking the tour and reading the help center pages to learn how the site works before you begin posting, as was suggested when you created your account. — Ken White

George Joseph George Joseph · Accepted Answer · 2022-07-29T00:35:22

The easy way to do this would be to use analytical functions (rank/row_number()).

If i got your question correct, you are looking to get the top 2 records by ignoring ties within them?

SELECT Y.* FROM (
SELECT X.*,ROW_NUMBER() OVER(PARTITION BY RNK ORDER BY TABLE1.DATE DESC) as rnk2 FROM (
    SELECT Table1.DATE,
           Table1.OfferID,
           Table2.ID,
           DENSE_RANK() OVER(ORDER BY Table1.DATE DESC) as rnk
    FROM TABLE1
    JOIN TABL2 ON TABLE1.ID = TABLE2.ID
    WHERE TABLE2.LASTNAME = "DOE"
               )X
WHERE X.RNK<=2
  )Y
WHERE Y.RNK2=1

Select the greater date if the secondary key is same

1 Answers