CODEWITHSUNDEEP
sql-servermax
R_life_R
R_life_R

Reputation: 816

Get Max value for each User in a specific period SQL

I have the following query:

I am trying to select for each ACCOUNT_ID in the Table PAYMENT its maximum AMOUNT value. It gives me a result but I suspect I am missing ACCOUNT_ID rows (I should get more ACCOUNT_ID)

SELECT a.REQUEST_DATE as PartyID, a.AMOUNT
FROM admin_all.PAYMENT a
LEFT OUTER JOIN admin_all.PAYMENT b
    ON a.ACCOUNT_ID = b.ACCOUNT_ID AND a.AMOUNT < b.AMOUNT 
WHERE b.ACCOUNT_ID IS NULL and a.TYPE='DEPOSIT' and a.STATUS='COMPLETED' 
and (a.REQUEST_DATE between '2019-12-01' and '2019-12-17')

Can anybody indicate if my select is correct?

Upvotes: 0

Views: 33

Answers (2)

Ryan Wilson
Ryan Wilson

Reputation: 10765

--You can USE ROW_NUMBER() with OVER and PARTITION BY to break up the records 
--into numbered rows ordered by the highest AMOUNT (DESC) and partitioned by the ID
--Then grab each record with a ROWNUM = 1
SELECT x.[PartyID], x.[AMOUNT]
FROM
(
    SELECT a.REQUEST_DATE as PartyID, a.AMOUNT, ROW_NUMBER() OVER (PARTITION BY a.ACCOUNT_ID ORDER BY a.AMOUNT DESC) AS [RowNum]
    FROM admin_all.PAYMENT a
    LEFT OUTER JOIN admin_all.PAYMENT b
        ON a.ACCOUNT_ID = b.ACCOUNT_ID AND a.AMOUNT < b.AMOUNT 
    WHERE b.ACCOUNT_ID IS NULL and a.TYPE='DEPOSIT' and a.STATUS='COMPLETED' 
    and (a.REQUEST_DATE between '2019-12-01' and '2019-12-17')
) AS x
WHERE x.[RowNum] = 1

Upvotes: 0

Alexey Usharovski
Alexey Usharovski

Reputation: 1442

Maybe something like this

SELECT a.ACCOUNT_ID, MAX(a.AMOUNT)
FROM admin_all.PAYMENT a
WHERE a.TYPE='DEPOSIT' and a.STATUS='COMPLETED' 
and (a.REQUEST_DATE between '2019-12-01' and '2019-12-17')
GROUP BY a.ACCOUNT_ID

Upvotes: 1

Related Questions