Select the greater date if the secondary key is same-CodePudding

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

CodePudding user response：

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