Ranking Functions: ROW_NUMBER

Ranking functions are used to assign rank to each and every row in a result set. There are four types of ranks offered by SQL Server 2008: ROW_NUMBER, RANK, DENSE_RANK and NTILE.

ROW_NUMBER is used to assign a number starting from 1 to n in the order specified by user.  In spite of the column on which ROW_NUMBER to be applied is having repeated values, different row number is assigned to each row.
e.g.
Select * , (row_number() over(order by au_lname)) as [row_number] from pubs.dbo.authors;

As shown in the above result set, row no. 17 and 18 has the same 'au_lname' but these have different row numbers.
row_number can also be used with an aggregate to provide sequencing within each group. This can be done using partition by clause e.g.
The following query partitions the rows based on the column 'city' and within each partition sorts the rows based on the column 'au_lname' .
select* , (row_number() over(partition by city order by au_lname)) as [row_number] from pubs.dbo.authors;

Each group starts row number from 1. Above result set is partitioned based on city. As one can see that city 'Berkeley' comes two times in the result set so first row with city 'Berkeley' has row_number 1 and the second row with the same city has row_number 2.

ROW_NUMBER in server side paging: Many times we have to display the information that spans across various web pages. In that case we want to display fixed no. of records from the result set at one time. Then comes the real application of row_number.

Below is the query that displays the rows starting from row number  7 till row number 20.

select *

from(select *, (row_number() over(order by au_lname)) as row_number from pubs.dbo.authors) as a

where row_number between 7 and 20; 

Intesect Operator

Intersect Operator allows user to display the common records from both the result sets. Like union operator, it also has some rules to follow like:

• If two result sets are being combined using union operator then both the result sets must contain same  number of columns.
• The data type of the corresponding columns in both the result sets must be same. If not then it must be  converted so that both have the same type.
• The final result set use the column names from the first result set.
• The order by clause is used at the end of the last select statement.
• The order by clause uses the column name(s) from the first select statement.
• Each select statement should include its own filtering criterion.

e.g. if we want to know the id’s of all the customers who have placed order then we can use intersect operator

select customerID as cid from AdventureWorks.sales.customer

INTERSECT

select employeeid as eid from AdventureWorks.humanresources.employee

order by cid

Except Operator

Except operator compares the result sets from two separate queries and returns the resultant subset. The final result set returns all the rows from the table mentioned on the left side of the except operator avoiding any matching rows pertaining to the table specified on the right side of the except operator. Like Union Operator, it also have some rules to follow like:

• If two result sets are being combined using union operator then both the result sets must contain same number of columns.
• The data type of the corresponding columns in both the result sets must be same. If not then it must be converted so that both have the same type.
• The final result set use the column names from the first result set.
• The order by clause is used at the end of the last select statement.
• The order by clause uses the column name(s) from the first select statement.
• Each select statement should include its own filtering criterion.

e.g.

select customerid as c from AdventureWorks.sales.customer

except

select customerid as cc from AdventureWorks.sales.salesorderheader order by c

Union Operator

Union operator allows the user to combine the result sets from multiple queries into one.  To use this operator, certain rules have to be followed:

• If two result sets are being combined using union operator then both the result sets must contain same number of columns.
• The data type of the corresponding columns in both the result sets must be same. If not then it must be converted so that both have the same type.
• Final result set uses the column names from the first result set.
• Order by clause is used at the end of the last select statement.
•  Order by clause uses the column name(s) from the first select statement.
•  Each select statement should include its own filtering criterion.

e.g.

select customerID as cid from sales.customer

where customerID < 50

union

select employeeid as eid from humanresources.employee

where employeeID < 50

order by cid

“Union all” is used to return all rows including duplicate ones too.