user2302158 user2302158 - 1 year ago 58
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 Source

You can use ROW_NUMBER for this:

SELECT ID, Account, Amount
  SELECT ID, Account, Amount,
                            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.