SQL with Manoj

In my [previous post] we learned about MS SQL Server 2005 Ranking functions.

Here we’ll see more scenarios where they can be used. We can use them with CTE i.e. Common Table Expressions, again a new gem with ver. 2005.

with myCTE as (
    select row_number() over(partition by class order by marks desc) as [RowNumber],
    class, marks, stuName from #tempTable)
select * from myCTE 
where RowNumber < 5

This gives following result:
row_num class marks name
1       A     90    pooja
2       A     90    saurabh
3       A     80    kanchan
4       A     80    manoj
1       B     90    paras
2       B     80    dinesh
3       B     70    hema
4       B     70    kapil

A CTE (Common Table Expression) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

More on CTE: http://msdn.microsoft.com/en-us/library/ms190766.aspx

The above mentioned PARTITION BY clause can also be used with SQL aggregate functions, like: AVG, COUNT, MIN, MAX, etc.

Here is an example:

select class, stuName, marks,
    AVG(marks) over(partition by class) AS [avg],
    SUM(marks) over(partition by class) AS [sum],
    MIN(marks) over(partition by class) AS [max],
    MAX(marks) over(partition by class) AS [min],
    COUNT(marks) over(partition by class) AS [count],
    STDEV(marks) over(partition by class) AS [ST_Dev],
    VAR(marks) over(partition by class) AS [VAR]
from #tempTable

This gives following result:
class name     marks AVG  SUM  MIN  MAX  COUNT STDEV   VAR
A     anita    50    76   460  50   90   6     15.0554 226.6667
A     harish   70    76   460  50   90   6     15.0554 226.6667
A     kanchan  80    76   460  50   90   6     15.0554 226.6667
A     manoj    80    76   460  50   90   6     15.0554 226.6667
A     pooja    90    76   460  50   90   6     15.0554 226.6667
A     saurabh  90    76   460  50   90   6     15.0554 226.6667
B     kamar    50    67   470  50   90   7     14.9603 223.8095
B     lalit    50    67   470  50   90   7     14.9603 223.8095
B     nitin    60    67   470  50   90   7     14.9603 223.8095
B     hema     70    67   470  50   90   7     14.9603 223.8095
B     kapil    70    67   470  50   90   7     14.9603 223.8095
B     dinesh   80    67   470  50   90   7     14.9603 223.8095
B     paras    90    67   470  50   90   7     14.9603 223.8095

I’ve rounded the last 2 column values to 4 decimal places.