PostgreSQL LATERAL JOIN to LIMIT GROUP BY

Question

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.

score 3 · Accepted Answer · answered May 27 '17 at 05:38

Make use of windows analytical function row_number

Rextester Demo

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.

PostgreSQL LATERAL JOIN to LIMIT GROUP BY

1 Answers1