SQL involving MAX of two colums and Group BY

Question

So... i got a table like this:

id  group   number  year
1   1       1       2000
2   1       2       2000
3   1       1       2001
4   2       1       2000
5   2       2       2000
6   2       1       2001
7   2       2       2001
8   2       3       2001

And i need to select the bigger number of the bigger year for each group. So i expect the result of the exemple to be:

3   1       1       2001
8   2       3       2001

any ideias? OBS: using Postgres

score 2 · Answer 1 · answered Mar 18 '15 at 20:58

2

If it's just certain rows you want to get you can use DISTINCT. If you want different maximums on the same rows you could use GROUP BY

SELECT DISTINCT ON ("group") * FROM tbl
  ORDER BY "group", year DESC, id DESC;

answered Mar 18 '15 at 20:58

Simo Kivistö

4,247
3
38
42

score 2 · Accepted Answer · answered Mar 18 '15 at 21:01

2

SELECT *
FROM (
  SELECT *,
    row_number() over (partition by "group" order by "year" desc, "number" desc ) x
  FROM table1
) x
WHERE x = 1;

demo: http://sqlfiddle.com/#!15/cd78e/2

answered Mar 18 '15 at 21:01

krokodilko

35,300
7
55
79

It seens that it works fine. But i'm using HQL and i can not use subqueries in "FROM ...." only on "SELECT" or " WHERE". :/ – Vinicius Tafuri Carvalho Mar 19 '15 at 18:43
You can create a view in the database using this query, and in HQL you can use just: `select * from view_name` – krokodilko Mar 20 '15 at 07:13

SQL involving MAX of two colums and Group BY

2 Answers2