Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ExamEntry Student_ID Grade
- 11 1 80
- 12 2 70
- 13 3 20
- 14 3 68
- 15 4 75
- SELECT
- Student_ID
- , MAX(Grade) AS maxGrade
- FROM table_name
- GROUP BY Student_ID
- HAVING maxGrade > MINIMUM_GRADE_NEEDED
- SELECT
- Student_ID
- , Grade
- FROM table_name
- WHERE ExamEntry IN (
- SELECT
- MAX(ExamEntry)
- FROM table_name
- GROUP BY Student_ID
- )
- HAVING Grade > MINIMUM_GRADE_NEEDED
- SELECT student_id, MAX(ExamEntry)
- FROM data
- WHERE Grade > :threshold
- GROUP BY student_id
- select s.id, s.name, t.name, max(r.score)
- from student s
- left outer join test_result r on r.student_id = s.id
- left outer join test t on r.test_id = t.id
- group by s.id, s.name, t.name
- select s.id, s.name, max(r.score)
- from student s
- left outer join test_result r on r.student_id = s.id
- group by s.id, s.name
- CREATE INDEX [IX_results] ON [dbo].[results]
- (
- [Grade],
- [Student_ID],
- [ExamEntry]
- )
- ON [PRIMARY]
- SELECT
- Student_ID
- FROM
- [results]
- WHERE
- Grade >= 60
- GROUP BY
- Student_ID
- CREATE INDEX [IX_results] ON [dbo].[results]
- (
- [Student_ID],
- [ExamEntry],
- [Grade]
- )
- ON [PRIMARY]
- SELECT
- *
- FROM
- [results]
- WHERE
- [results].ExamEntry = (
- SELECT
- MAX([student_results].ExamEntry)
- FROM
- [results] AS [student_results]
- WHERE
- [student_results].Student_ID = [results].student_id
- )
- AND [results].Grade > 60
- Method QueryCost(No Index) QueryCost(WithIndex)
- 1 23% 9%
- 2 38% 46%
- 3 38% 46%
- select examentry,student_id,grade
- from data
- where examentry in
- (select max(examentry)
- from data
- where grade > 60
- group by student_id)
- SELECT ExamEntry, Student_ID, Grade from data,
- ( SELECT max(ExamEntry) as ExId GROUP BY Student_ID) as newdata
- WHERE `data`.`ExamEntry`=`newdata`.`ExId` AND Grade > 60;
- where grade > 60
- where grade between 60 and 100
Add Comment
Please, Sign In to add comment