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 ...