ADO.NET 3.5 Cookbook, 2nd Edition

4.14. Retrieving a Ranked Result Set

Problem

You need to rank records in a result set based on the values in one or more fields.

Solution

Two solutions are presented. The first solution uses the ROW_NUMBER() ranking function to rank records from the Person.Contact table in AdventureWorks based on the LastName and FirstName values. The top 10 records are output.

The C# code in Program.cs in the project RetrieveRankedResultSet is shown in Example 4-15.

Example 4-15. File: Program.cs for RetrieveRankedResultSet solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace RetrieveRankedResultSet
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT TOP 10 ROW_NUMBER() " +
                "OVER(ORDER BY LastName, FirstName) Rank, " +
                "ContactID, FirstName, LastName " +
                "FROM Person.Contact";

            // Fill the DataTable
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.Fill(dt);

            Console.WriteLine("Rank\tID\tName");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0}\t{1}\t{2}, {3}",
                    row["Rank"], row["ContactID"], row["LastName"], row["FirstName"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is shown in Figure 4-15.

Figure 4-15. Output for RetrieveRankedResultSet solution

The second solution uses the ROW_NUMBER() ranking function and the PARTITION BY clause to rank records from the Person.Contact table in AdventureWorks within each ManagerID based on the LastName and FirstName values. The top 20 records are output.

The C# code in Program.cs in the project RetrieveRankedPartitionedResultSet is shown in Example 4-16.

Example 4-16. File: Program.cs for RetrieveRankedPartitionedResultSet solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace RetrieveRankedPartitionedResultSet
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT TOP 20 ManagerID, ROW_NUMBER() " +
                "OVER(PARTITION BY ManagerID ORDER BY LastName, FirstName) Rank, " +
                "e.ContactID, FirstName, LastName " +
                "FROM HumanResources.Employee e " +
                " LEFT JOIN Person.Contact c " +
                " ON e.ContactID = c.ContactID";

            // Fill the DataTable
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.Fill(dt);

            Console.WriteLine("MgrID\tRank\tID\tName");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0}\t{1}\t{2}\t{3}, {4}",
                    row["ManagerID"], row["Rank"], row["ContactID"],
                    row["LastName"], row["FirstName"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is shown in Figure 4-16.

Figure 4-16. Output for RetrieveRankedPartitionedResultSet solution

Discussion

SQL Server 2005 introduced three new ranking functions: ROW_NUMBER(), DENSE_RANK(), and NTILE(). This is in addition to the RANK() function available in SQL Server 2000. These are discussed in the following subsections.

ROW_NUMBER()

The ROW_NUMBER() function returns the number of a row within a result set starting with 1 for the first row. The ROW_NUMBER() function does not execute until after a WHERE clause is used to select the subset of data.

The ROW_NUMBER() function syntax is:

	ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)

Where:

<partition_by_clause>

Divides the result set into groups to which the ROW_NUMBER() function is applied. The function is applied to each partition separately; computation restarts for each partition.

<order_by_clause>

Specifies the order in which the sequential ROW_NUMBER() values are assigned.

RANK()

The RANK() function returns the rank of each row in a result set starting with 1. If two or more rows ties for rank, the same value is assigned to each tied row with numbering continuing normally (i.e., leaving a gap) with the non-tied row.

The RANK() function syntax is:

	RANK() OVER ([<partition_by_clause>] <order_by_clause>)

Where:

<partition_by_clause>

Divides the result set into groups to which the RANK() function is applied. The function is applied to each partition separately; computation restarts for each partition.

<order_by_clause>

Specifies the order in which the sequential RANK() values are assigned.

DENSE_RANK()

The DENSE_RANK() function returns the rank of rows in a result set without gaps in the ranking. This is similar to the RANK() function, except in cases where more than one row receives the same ranking. In this case, the rank value for a row following a group of rows with the same rank is the rank value for the rows in the group plus 1 rather than the next row number. For example, if the values a, b, b, c, d, e were being ranked, RANK() would return 1, 2, 2, 4, 5 while DENSE_RANK() would return 1, 2, 2, 3, 4.

The DENSE_RANK() function syntax is:

	DENSE_RANK() OVER ([<partition_by_clause>] <order_by_clause>)

Where:

<partition_by_clause>

Divides the result set into groups to which the DENSE_RANK() function is applied. The function is applied to each partition separately; computation restarts for each partition.

<order_by_clause>

Specifies the order in which the sequential DENSE_RANK() values are assigned.

NTILE()

The NTILE() function returns the group in which a row belongs within an ordered distribution of groups. Group numbering starts with 1.

The NTILE() function syntax is:

	NTILE(n) OVER ([<partition_by_clause>] <order_by_clause>)

Where:

n

Specifies the number of groups that each partition should be divided into.

<partition_by_clause>

Divides the result set into groups to which the NTILE() function is applied. The function is applied to each partition separately; computation restarts for each partition.

<order_by_clause>

Specifies the column used to define the groups to which the NTILE() function is applied.