How to get highest value for each group by checking with two columns value

I have this table test_table below

USER_ID | YEAR | MONEY
----------------------
  1     |  0   |  0
  1     | 12   | 12
  1     | 48   | 12
  2     | 15   | 15
  2     | 10   | 20
  3     |  0   |  0

So I am trying to return the row which has the highest money. For example, the row return would be like this

USER_ID | YEAR | MONEY
----------------------
  1     | 12   | 12
  1     | 48   | 12
  2     | 10   | 20
  3     |  0   |  0

But because User ID 1 has the same value for money, I would like to check for the highest year of that money amount and return the result. The expected result should be

USER_ID | YEAR | MONEY
----------------------
  1     | 48   | 12
  2     | 10   | 20
  3     |  0   |  0

Is it possible to get row like this?

Here is the link to test your query online http://sqlfiddle.com/#!9/2e5660/1

Answer

You can try using correlated subquery

DEMO

select userid, moneyval,max(year) as year
from
(
select * from t a
where moneyval in 
(select max(moneyval) from t b where a.userid=b.userid)
)A group by userid, moneyval

OUTPUT:

userid  moneyval    year
1        12          48
2        20          10
3        0           0

Advertisement

Answer