select top n record from each group sqlite

You could use a correlated subquery:

select  *from    ResultView rv1where   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.

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 aWHERE 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 | total1         | 1         | 1       | 891         | 2         | 1       | 77

Example of top 2 by SubjectId, ordered by total:

SELECT * FROM stack AS aWHERE 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 | total1         | 1         | 1       | 891         | 2         | 1       | 772         | 4         | 1       | 602         | 5         | 1       | 55

I hope that was the answer you were looking for.