0
ID Payment year
A 10 1
A 15 2
A 12 3
B 11 2
B 15 4
C 25 1
C 17 3

I'm looking for a query that returns a row for each ID for its last year. The year column is ordered increasing for each ID.

ID Payment year
A 12 3
B 15 4
C 17 3 
select ID, Payment, Year from payment_table
where year = (select max(year) from ?????????);

what shall I write instead of "????????"?

philipxy
  • 14,867
  • 6
  • 39
  • 83

1 Answers1

3

Use subquery :

select t.*
from table t
where year = (select max(t1.year) from table t1 where t1.id = t.id);
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • `table` as a table name is probably a poor choice and can lead to confusion. – lurker Jun 27 '18 at 16:41
  • @shayan `select t.* from table t...` means you are selecting from a table named `table` and referring to that table as `t` in any subsequent expression. The inner `select max(t1.year) from table t1...` is a select from the same table named `table` and referring to that one as `t1` so as not to be confused with the results of the first select for `t`. – lurker Jun 27 '18 at 16:43
  • @shayan. . . `table` is dummy name of table & `t1` is a table alise. So, you just rename it with your original table name. – Yogesh Sharma Jun 27 '18 at 16:43
  • @shayan Replace `table` with `payment_table`. Hopefully this is a production database that you mess around. You seem to know nothing about SQL. This is as one of the most basic query, and you can't even figure out what to change. I suggest you stop messing around with it, and hire someone who knows database. – Eric Jun 27 '18 at 17:36