select top n record from each group sqlite

Question

I am trying to select top 2 records from a database table result that looks like this

SubjectId |  StudentId | Levelid | total
------------------------------------------
 1        |  1         |   1     | 89
 1        |  2         |   1     | 77
 1        |  3         |   1     | 61
 2        |  4         |   1     | 60
 2        |  5         |   1     | 55
 2        |  6         |   1     | 45

i tried this query

SELECT rv.subjectid,
       rv.total,
       rv.Studentid,
       rv.levelid
  FROM ResultView rv
       LEFT JOIN ResultView rv2
              ON ( rv.subjectid = rv2.subjectid 
    AND
rv.total <= rv2.total ) 
 GROUP BY rv.subjectid,
          rv.total,
          rv.Studentid
HAVING COUNT( * ) <= 2
order by rv.subjectid desc

but some subjects like where missing, i even tried the suggestiong frm the following link

How to select the first N rows of each group?

but i get more that two for each subjectid

what am i doing wrong?

`HAVING COUNT( * ) <= 3` filters out any group with 3 or more rows. But you're not looking to exclude entire groups, just any rows past the second one. — Andomar, Jan 23 '15 at 22:00
the `HAVING COUNT( * ) <= 3` was a typo, it has been corrected — Smith, Jan 23 '15 at 22:08
If you provide an example of the desired output, it is easier to provide an answer. — Johanneke, Jun 07 '17 at 17:09

score 9 · Accepted Answer · answered Jan 23 '15 at 21:55

You could use a correlated subquery:

select  *
from    ResultView rv1
where   SubjectId || '-' || StudentId || '-' || LevelId in
        (
        select  SubjectId || '-' || StudentId || '-' || LevelId
        from    ResultView rv2
        where   SubjectID = rv1.SubjectID
        order by
                total desc
        limit   2
        )

This query constructs a single-column primary key by concatenating three columns. If you have a real primary key (like ResultViewID) you can substitute that for SubjectId || '-' || StudentId || '-' || LevelId.

Example at SQL Fiddle.

thanks for the answer, but i am getting a different result. The example i posted above is a simplified version of my problem, maybe that why. i will post a more details version. The table I am selecting from is actually a view which joins two tables by a common id . if i want to select this result based in the levelid, where do i put the `where` clause? — Smith, Jan 24 '15 at 04:23

score 8 · Answer 2 · answered Jun 07 '17 at 16:49

I hope I'm understanding your question correctly. Let me know if this is correct:

I recreated your table:

CREATE TABLE stack (
       SubjectId INTEGER(10),
       StudentId INTEGER(10),
       Levelid INTEGER(10),
       total INTEGER(10)
       )
;

Inserted values

INSERT INTO stack VALUES
       (1,1,1,89),
       (1,2,1,77),
       (1,3,1,61),
       (2,4,1,60),
       (2,5,1,55),
       (2,6,1,45)
;

If you're trying to get the top group by Levelid (orderd by total field, assuming StudentID as primary key):

SELECT * 
FROM stack AS a
WHERE a.StudentID IN (
      SELECT b.StudentID
      FROM stack AS b
      WHERE a.levelid = b.levelid
      ORDER BY b.total DESC
      LIMIT 2
      )
;

Yields this result:

SubjectId | StudentId | Levelid | total
1         | 1         | 1       | 89
1         | 2         | 1       | 77

Example of top 2 by SubjectId, ordered by total:

SELECT * 
FROM stack AS a
WHERE a.StudentID IN (
      SELECT b.StudentID
      FROM stack AS b
      WHERE a.subjectID = b.subjectID
      ORDER BY b.total DESC
      LIMIT 2
      )
;

Result:

SubjectId | StudentId | Levelid | total
1         | 1         | 1       | 89
1         | 2         | 1       | 77
2         | 4         | 1       | 60
2         | 5         | 1       | 55

I hope that was the answer you were looking for.

The good news that it works, the bad news is that it takes 30 seconds to execute per 10K records. — Hamzeh Soboh, Jun 01 '20 at 21:48

score 2 · Answer 3 · answered Jul 14 '22 at 18:46

ROW_NUMBER window function

SQLite now supports window functions, so the exact same code that works for PostgreSQL at Grouped LIMIT in PostgreSQL: show the first N rows for each group? now also works for SQLite.

This could be potentially faster than the other answers so far as it does not run a correlated subquery.

Supposing you want to get the 2 highest total rows for each StudentID:

SELECT *
FROM (
    SELECT
      ROW_NUMBER() OVER (
        PARTITION BY "StudentID"
        ORDER BY "total" DESC
      ) AS "rnk",
      *
    FROM "mytable"
  ) sub
WHERE
  "sub"."rnk" <= 2
ORDER BY
  "sub"."StudentID" ASC,
  "sub"."total" DESC

Tested on SQLite 3.34, PostgreSQL 14.3. GitHub upstream

select top n record from each group sqlite

3 Answers3

Linked