I'm writing a Web-based application that will retrieve data from a Microsoft SQL Server 2005 database. The application includes a simple report that provides the total annual sales per district, according to the year that the user enters. I want to rank the data by sales amount as the application retrieves the rows. For example, if the Southwest district has the most sales for a particular year, I want that district to be ranked number one and appear in the first row returned from the database. How do I include in a SQL Server stored procedure the logic necessary to support this functionality?
You're in luck! SQL Server 2005 includes a set of functions that analyze and rank values returned by a query. What takes several lines of code to achieve in earlier SQL Server versions can now be condensed into a single function and its arguments. Simply include the function as a column definition in the SELECT clause of your query, and SQL Server analyzes the specified values and automatically assigns a ranking value to each row, based on that analysis. . . .
