Reputation: 113
Sorry I'm just failing to do the lateral join!
I got a table like this:
ID | NUMBER | VALUE
-------------------
20 | 12 | 0.7
21 | 12 | 0.8
22 | 13 | 0.8
23 | 13 | 0.7
24 | 13 | 0.9
25 | Null | 0.9
Now I would like to get the first 2 rows for each NUMBER sorted by decreasing order of VALUE.
ID | NUMBER | VALUE
-------------------
21 | 12 | 0.8
20 | 12 | 0.7
24 | 13 | 0.9
22 | 13 | 0.8
The code I tried so far looks like this: (Found: Grouped LIMIT in PostgreSQL: show the first N rows for each group?)
SELECT DISTINCT t_outer.id, t_top.number, t_top.value
FROM table t_outer
JOIN LATERAL (
SELECT * FROM table t_inner
WHERE t_inner.number NOTNULL
AND t_inner.id = t_outer.id
AND t_inner.number = t_outer.number
ORDER BY t_inner.value DESC
LIMIT 2
) t_top ON TRUE
order by t_outer.value DESC;
Everything is fine so far, it just seems like the LIMIT 2 is not working. I get all the rows for all NUMBER elements back.
Upvotes: 3
Views: 3146
Reputation: 8143
Make use of windows analytical function row_number
select "ID", "NUMBER", "VALUE" from
(select t.*
,row_number() over (partition by "NUMBER"
order by "VALUE" desc
) as rno
from table1 t
) t1
where t1.rno <=2;
Output
ID NUMBER VALUE
21 12 0,8000
20 12 0,7000
24 13 0,9000
22 13 0,8000
25 NULL 0,9000
Explanation:
Inner query t1, will assing rno
order by value
desc for each number
group. Then in outer query, you can select rno <= 2
to get your output.
Upvotes: 3