code_junkie

Fetch data with single and fast SQL query

Nov 13th, 2011
121
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.19 KB | None | 0 0
  1. ExamEntry Student_ID Grade
  2. 11 1 80
  3. 12 2 70
  4. 13 3 20
  5. 14 3 68
  6. 15 4 75
  7.  
  8. SELECT
  9. Student_ID
  10. , MAX(Grade) AS maxGrade
  11. FROM table_name
  12. GROUP BY Student_ID
  13. HAVING maxGrade > MINIMUM_GRADE_NEEDED
  14.  
  15. SELECT
  16. Student_ID
  17. , Grade
  18. FROM table_name
  19. WHERE ExamEntry IN (
  20. SELECT
  21. MAX(ExamEntry)
  22. FROM table_name
  23. GROUP BY Student_ID
  24. )
  25. HAVING Grade > MINIMUM_GRADE_NEEDED
  26.  
  27. SELECT student_id, MAX(ExamEntry)
  28. FROM data
  29. WHERE Grade > :threshold
  30. GROUP BY student_id
  31.  
  32. select s.id, s.name, t.name, max(r.score)
  33. from student s
  34. left outer join test_result r on r.student_id = s.id
  35. left outer join test t on r.test_id = t.id
  36. group by s.id, s.name, t.name
  37.  
  38. select s.id, s.name, max(r.score)
  39. from student s
  40. left outer join test_result r on r.student_id = s.id
  41. group by s.id, s.name
  42.  
  43. CREATE INDEX [IX_results] ON [dbo].[results]
  44. (
  45. [Grade],
  46. [Student_ID],
  47. [ExamEntry]
  48. )
  49. ON [PRIMARY]
  50.  
  51. SELECT
  52. Student_ID
  53. FROM
  54. [results]
  55. WHERE
  56. Grade >= 60
  57. GROUP BY
  58. Student_ID
  59.  
  60. CREATE INDEX [IX_results] ON [dbo].[results]
  61. (
  62. [Student_ID],
  63. [ExamEntry],
  64. [Grade]
  65. )
  66. ON [PRIMARY]
  67.  
  68. SELECT
  69. *
  70. FROM
  71. [results]
  72. WHERE
  73. [results].ExamEntry = (
  74. SELECT
  75. MAX([student_results].ExamEntry)
  76. FROM
  77. [results] AS [student_results]
  78. WHERE
  79. [student_results].Student_ID = [results].student_id
  80. )
  81. AND [results].Grade > 60
  82.  
  83. Method QueryCost(No Index) QueryCost(WithIndex)
  84. 1 23% 9%
  85. 2 38% 46%
  86. 3 38% 46%
  87.  
  88. select examentry,student_id,grade
  89. from data
  90. where examentry in
  91. (select max(examentry)
  92. from data
  93. where grade > 60
  94. group by student_id)
  95.  
  96. SELECT ExamEntry, Student_ID, Grade from data,
  97. ( SELECT max(ExamEntry) as ExId GROUP BY Student_ID) as newdata
  98. WHERE `data`.`ExamEntry`=`newdata`.`ExId` AND Grade > 60;
  99.  
  100. where grade > 60
  101.  
  102. where grade between 60 and 100
Add Comment
Please, Sign In to add comment