I have this table test_table below
x
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
Advertisement
Answer
You can try using correlated subquery
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