CODEWITHSUNDEEP
sqlpostgresqlgroup-bysql-order-by
Slook
Slook

Reputation: 83

LIMIT by GROUP in SQl (postgresql)

Below is my table students (having 100K+ rows in orignal, showing just a set):

id name class marks
1 Ryan 5 8
2 Max 5 7
3 Max1 5 10
4 Ryan1 6 8
5 Max2 6 10
6 Ryan2 6 7
7 Ryan3 7 8
8 Max3 7 10
9 Ryan4 7 7

I want to fetch two rows per class ( 5 & 7) having marks <= 10 , also sorted by class, marks ASC

So, the expected result will be:-

id name class marks
1 Ryan 5 8
3 Max1 5 10
7 Ryan3 7 8
8 Max3 7 10

To execute below I tried:-

SELECT DISTINCT t_out.class, t_top.marks, t_top.name 
FROM students t_out 
  JOIN LATERAL (
    select * 
    from students t_in 
    where t_in.class = t_out.class 
    ORDER BY t_in.id ASC
  ) t_top ON TRUE 
WHERE t_top.marks <= 10 
 AND (t_out.class = 5 OR t_out.class = 7) 
ORDER BY t_top.marks DESC LIMIT 2;

Result on original database:- it's loading since long time

Result on sample :- Error: near line 20: near "(": syntax error

Is 10 the highest marks?

Upvotes: 0

Views: 1986

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You would use row_number():

select s.*
from (select s.*,
             row_number() over (partition by class order by marks desc) as seqnum
      from students s
      where marks < 10 and class in (5, 7)
     ) s
where seqnum <= 2
order by class, marks;

Note: Your question is a little confusing. You seem to want two rows with the highest marks per class ordered in descending order by marks.

EDIT:

Based on your comment:

select s.*
from (select s.*,
             row_number() over (partition by class order by marks desc) as seqnum,
             count(*) over (partition by class) as cnt
      from students s
      where marks < 10 
     ) s
where seqnum <= 2 and cnt >= 2
order by class, marks;

Upvotes: 1

Related Questions