LIMIT by GROUP in SQl (postgresql)

Question

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?

The ORDER BY in the derived table (sub-query) is pretty much useless. — user330315
– user330315, Commented Aug 28, 2021 at 17:53

Gordon Linoff · Accepted Answer · 2021-08-28 18:34:33Z

1

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;

edited Aug 28, 2021 at 18:34

answered Aug 28, 2021 at 17:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Slook Over a year ago

Is latrel not faster than window? I'll give try for this on my main table

Slook Over a year ago

There are 99% possibility that in original database 10 marks is not highest and 7 is not lowest. And that's why query will be fetched DESC but output should be in ASC

Gordon Linoff Over a year ago

@Slook . . . You would need to test on your data, but appropriate indexes for filtering is probably the key to performance.

Slook Over a year ago

a small query, why we are not using seqnum = 2 ? As I want to exclude those class where class is having only 1 row (suppose here class 7)

Gordon Linoff Over a year ago

@Slook . . . Check out the edit. I didn't realize that was how you wanted to filter the classes.

|

Collectives™ on Stack Overflow

LIMIT by GROUP in SQl (postgresql)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related