Friday, February 3, 2012

How to rank results and aggregate without Order By in SQL

Hack the Gibson -

Here’s the idea, you are a teacher and have input your students grades from the semester.  So the idea is we want to to look at how we can AVG (average) their grades out without having to perform an Order By on all the fields.  Then, we want to rank the results by their grades – listing the highest grade first.

So, our table will look like this -

first_name last_name student_number grade
John Smith 1234 62
Jane Doe 4321 88
John Smith 1234 99
Frank Oz 6784 85
John Smith 1234 77

So the first order of business would be to average out the grades.  Without the First and Last names this would be meaningless.  Additionally, the student_number is our key field.
So our SQL Select statement could look like this -

Select 



         Distinct(g.student_number) As 'Student ID'



    ,    g.first_name As 'First Name'



    ,    g.last_name As 'Last Name'



    ,    (Select



            AVG(grade)



           From dbo.grades gavg



            Where gavg.student_number = g.student_number



            ) As 'Grade'



From dbo.grades g




Notice that my SELECT statement does not contain a Order By Clause.  This is because of the nested SELECT statement.  The magic is in the Where statement of the nested SELECT.  It allows us to separate the grade values so that we are not getting the entire AVG of the column from the table but we are separating them based on the current student_number. 



The Distinct keyword allows us to return only one occurrence of  the value in the column.  However, if you have two students with the same student number but different names, it will return two rows signifying that not all the columns have the same value.



Now to move into the ranking.  Basically we will take the following table and have it rank for us like so -












































first_name last_name student_number grade
John Smith 1234 77
Jane Doe 4321 89
Frank Oz 6784 99





Then we will employ the power of SQL to rank the results for us like so -





Select



         g.first_name



    ,    g.last_name



    ,    g.student_number



    ,    g.grade



    ,    ROW_NUMBER()OVER 



            (Order By g.grade DESC) As 'rank'



From dbo.grades g




What does this do?  It will return the following -




















































first_name last_name student_number grade rank
Frank Oz 6784 99 1
Jane Doe 4321 89 2
John Smith 1234 77 3



Not only does it give you a numeric value for the representation of rank, it will order by in the correct order so that your result set is in the correct order.  No fussing with it in the ASP side.  The numeric value is helpful if you want to do something like highlight your GridView for the top ten results (Or I have even seen progress bar type representations to relevance).



In any case, use the power of SQL before trying to manipulate the data on the ASP side, it is faster and more efficient.  After all, it’s what it was designed to do.



Happy .Netting…Saleh

Hack the Gibson

No comments:

Post a Comment