sql - query in sqlite about second row -
i have following query second row in studentsubject table
it's work
but need way give better performance using subquery
select sbid , stid, max(mark) studentsubject mark <( select max(mark) studentsubject group sbid) group sbid order mark desc limit 5 note : have 5 courses , need second highest mark each course
-- -- file generated sqlitestudio v3.0.3 on الأربعاء نيسان 1 12:43:34 2015 -- -- text encoding used: windows-1256 -- pragma foreign_keys = off; begin transaction; -- table: subject create table subject ( subjectid [int identity] (1, 1) primary key, subjectname varchar (50) not null ); insert subject ( subjectid, subjectname ) values ( 1, 'c#' ); insert subject ( subjectid, subjectname ) values ( 2, 'c++' ); insert subject ( subjectid, subjectname ) values ( 3, 'javascript' ); insert subject ( subjectid, subjectname ) values ( 4, 'database' ); insert subject ( subjectid, subjectname ) values ( 5, 'asp.net' ); -- table: student create table student ( studentid [int identity] (1, 1) primary key, studentname varchar (50) not null ); insert student ( studentid, studentname ) values ( 1, 'ahmad ali' ); insert student ( studentid, studentname ) values ( 2, 'khaled ahmad' ); insert student ( studentid, studentname ) values ( 3, 'mohammad ahmad' ); insert student ( studentid, studentname ) values ( 4, 'mohammad a.sharif' ); insert student ( studentid, studentname ) values ( 5, 'mahmoud zakal' ); insert student ( studentid, studentname ) values ( 6, 'morad tamimi' ); insert student ( studentid, studentname ) values ( 7, 'khalid ahson' ); insert student ( studentid, studentname ) values ( 8, 'eyad buzzoum' ); insert student ( studentid, studentname ) values ( 9, 'almeqdad a.ateeleh' ); insert student ( studentid, studentname ) values ( 10, 'mohammad abu naser' ); -- table: studentsubject create table studentsubject ( stid int not null, sbid int not null, mark decimal (2, 2) not null, foreign key ( stid ) references student (studentid), foreign key ( sbid ) references subject (subjectid), primary key ( stid, sbid ) ); insert studentsubject ( stid, sbid, mark ) values ( 1, 1, 90.5 ); insert studentsubject ( stid, sbid, mark ) values ( 1, 2, 97.5 ); insert studentsubject ( stid, sbid, mark ) values ( 1, 3, 87.5 ); insert studentsubject ( stid, sbid, mark ) values ( 1, 4, 87 ); insert studentsubject ( stid, sbid, mark ) values ( 1, 5, 83 ); insert studentsubject ( stid, sbid, mark ) values ( 2, 1, 88 ); insert studentsubject ( stid, sbid, mark ) values ( 2, 2, 80 ); insert studentsubject ( stid, sbid, mark ) values ( 2, 3, 83 ); insert studentsubject ( stid, sbid, mark ) values ( 2, 4, 81 ); insert studentsubject ( stid, sbid, mark ) values ( 2, 5, 79 ); insert studentsubject ( stid, sbid, mark ) values ( 3, 5, 79 ); insert studentsubject ( stid, sbid, mark ) values ( 3, 1, 82 ); insert studentsubject ( stid, sbid, mark ) values ( 3, 2, 72 ); insert studentsubject ( stid, sbid, mark ) values ( 3, 3, 84 ); insert studentsubject ( stid, sbid, mark ) values ( 3, 4, 87 ); insert studentsubject ( stid, sbid, mark ) values ( 4, 5, 85 ); insert studentsubject ( stid, sbid, mark ) values ( 4, 1, 83 ); insert studentsubject ( stid, sbid, mark ) values ( 4, 2, 84 ); insert studentsubject ( stid, sbid, mark ) values ( 4, 3, 88 ); insert studentsubject ( stid, sbid, mark ) values ( 4, 4, 87 ); insert studentsubject ( stid, sbid, mark ) values ( 5, 1, 95.1 ); insert studentsubject ( stid, sbid, mark ) values ( 5, 2, 92.6 ); insert studentsubject ( stid, sbid, mark ) values ( 5, 3, 90.2 ); insert studentsubject ( stid, sbid, mark ) values ( 5, 4, 82.3 ); insert studentsubject ( stid, sbid, mark ) values ( 5, 5, 89.5 ); insert studentsubject ( stid, sbid, mark ) values ( 6, 1, 85.1 ); insert studentsubject ( stid, sbid, mark ) values ( 6, 2, 82.6 ); insert studentsubject ( stid, sbid, mark ) values ( 6, 3, 92.2 ); insert studentsubject ( stid, sbid, mark ) values ( 6, 4, 96.3 ); insert studentsubject ( stid, sbid, mark ) values ( 6, 5, 85.5 ); insert studentsubject ( stid, sbid, mark ) values ( 7, 1, 85.1 ); insert studentsubject ( stid, sbid, mark ) values ( 7, 2, 87.6 ); insert studentsubject ( stid, sbid, mark ) values ( 7, 3, 75.2 ); insert studentsubject ( stid, sbid, mark ) values ( 7, 4, 92.3 ); insert studentsubject ( stid, sbid, mark ) values ( 7, 5, 84.5 ); insert studentsubject ( stid, sbid, mark ) values ( 8, 1, 79.1 ); insert studentsubject ( stid, sbid, mark ) values ( 8, 2, 77.6 ); insert studentsubject ( stid, sbid, mark ) values ( 8, 3, 75.2 ); insert studentsubject ( stid, sbid, mark ) values ( 8, 4, 72.3 ); insert studentsubject ( stid, sbid, mark ) values ( 8, 5, 84.5 ); insert studentsubject ( stid, sbid, mark ) values ( 9, 1, 88.1 ); insert studentsubject ( stid, sbid, mark ) values ( 9, 2, 86.6 ); insert studentsubject ( stid, sbid, mark ) values ( 9, 3, 84.2 ); insert studentsubject ( stid, sbid, mark ) values ( 9, 4, 92.3 ); insert studentsubject ( stid, sbid, mark ) values ( 9, 5, 83.5 ); insert studentsubject ( stid, sbid, mark ) values ( 10, 1, 83.6 ); insert studentsubject ( stid, sbid, mark ) values ( 10, 2, 82.7 ); insert studentsubject ( stid, sbid, mark ) values ( 10, 3, 85.2 ); insert studentsubject ( stid, sbid, mark ) values ( 10, 4, 88.7 ); insert studentsubject ( stid, sbid, mark ) values ( 10, 5, 80.2 ); commit transaction;
you can offset reqult 2nd row , limit reqult on 4 fetch 5 rows
select sbid , stid, max(mark) studentsubject group sbid order mark desc limit 4 offset 1 
Comments
Post a Comment