user2302158 user2302158 - 6 months ago 8
SQL Question

How to get the max value id from sql table

I need to get a max (Amount) value of each Account from the below table

ID Account Amount
1 rx00 100
2 rx00 200
3 rx00 100
4 vxtt 50
5 vxtt 70
6 vxtt 80


I need a result table as

ID Account Amount
2 rx00 200
6 vxtt 80


Please advise to the above result

Answer

You can use ROW_NUMBER for this:

SELECT ID, Account, Amount
FROM (
  SELECT ID, Account, Amount,
         ROW_NUMBER() OVER (PARTITION BY Account 
                            ORDER BY Amount DESC) AS rn
  FROM mytable) AS t
WHERE t.rn = 1 

If you have ties, i.e. more than one records sharing the same maximum Amount value and you want to return all these records, then use RANK instead of ROW_NUMBER.