CODEWITHSUNDEEP
sqlsql-servert-sqlselect
Mario Vega
Mario Vega

Reputation: 67

Count current job with a partition

The next code makes a join between users and payment, to get the last payment. The query should work if the payment table did not contain duplicated rows with the same max_date as the following one.

Something to notice, is that the row is not completely duplicated, sometimes contains little changes. But we do not care if we select the 'right' one, we only need it to be one, no matter which one of those is.

user_ID | Payment | date     | product | credit_card
  1        300      1/1/2020     A            No
  1        300      1/1/2020     Null  |      No
  1        300      1/1/2021     A            Yes
  1        300      1/1/2021     Null  |      Yes

This causes the second inner join to duplicate rows because it makes a match twice with the maxDate which is 1/1/2021

SELECT  a.*, c.*
FROM users a 
    INNER JOIN payments c
        ON a.id = c.user_ID
    INNER JOIN
    (
        SELECT user_ID, MAX(date) maxDate
        FROM payments
        GROUP BY user_ID
    ) b ON c.user_ID = b.user_ID AND
            c.date = b.maxDate

I'm looking for a way to select only the first match of the maxDate. Any clue is welcome, thank in advance for any help.

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

You should be using window functions for this. That would be:

SELECT u.*, p.*
FROM users u JOIN
     (SELECT p.*,
             ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.date DESC) as seqnum
      FROM payments p
     ) p
     ON p.user_ID = u.id AND p.seqnum = 1;

This returns one row, but which row is arbitrary.

Note the use of meaningful table aliases in the query -- u for users and p for `payments. Don't use meaningless letters. They just make the query hard to read -- and to maintain.

Upvotes: 3

Related Questions