Month: July 2012

Using Row_Number in SQL

DaveLeave a comment

ROW_NUMBER is a ranking function which allow you to assign a number to each row of your query result. The numbering can be restarted based on one or more fields, this is known as partitioning (you may think of this as numbering on grouped rows, as grouping has another meaning in SQL terms lets stick with partitioning for now).

The syntax of the function is:

ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , … [ n ] ] order_by_clause )

Discaimer: The numbering the rows can only be gauranteed the same on each execution if all of the following are true;

  • The combined values of the partition columns are unique.
  • The combined values of the order by columnns are unique.
  • The values of the combined partition and order by columns are unique.

If any of these combinations have the same values they may be numbered differently.
If a standard ORDER BY clause is not mentioned in the query the order of the rows in the result will usually match that of the ORDER BY clause in the OVER clause. If I do specify a standard ORDER BY clause this will change the order of the rows in the result but the numbering will still be assigned based on the ORDER BY in the OVER clause.

In the following examples I will be using just two tables from the AdventureWorks (AdventureWorksLT2008) database. The tables are Product and ProductCategory

Example 1. Simple numbering each row in the result

This example demonstrates the simplest method of assigning row numbers. The numbering is assigned based on the p.ListPrice column in descending order.

SELECT
    ROW_NUMBER() OVER(ORDER BY p.ListPrice DESC) AS RowNum,
    p.ProductNumber,
    p.Name,
    p.ListPrice,
    c.Name AS CategoryName
FROM SalesLT.Product p
INNER JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID

Because I have not specified a standard ORDER BY clause in the query the results are displayed in the order of the rownum column. The results will not be ordered exactly the same on each execution because there are multiple rows with the same price. So the rows with the same price may, as a group of rows, be ordered differently with a different row number each time the query is run.

Product Number...... Name.......................... ListPrice. CategoryName.
1       BK-R93R-62   Road-150 Red, 62               3578.27    Road Bikes
2       BK-R93R-44   Road-150 Red, 44               3578.27    Road Bikes
3       BK-R93R-48   Road-150 Red, 48               3578.27    Road Bikes
4       BK-R93R-52   Road-150 Red, 52               3578.27    Road Bikes
5       BK-R93R-56   Road-150 Red, 56               3578.27    Road Bikes
6       BK-M82S-38   Mountain-100 Silver, 38        3399.99    Mountain Bikes
7       BK-M82S-42   Mountain-100 Silver, 42        3399.99    Mountain Bikes
8       BK-M82S-44   Mountain-100 Silver, 44        3399.99    Mountain Bikes
9       BK-M82S-48   Mountain-100 Silver, 48        3399.99    Mountain Bikes
10      BK-M82B-38   Mountain-100 Black, 38         3374.99    Mountain Bikes

Example 2. Partitioning on specific fields
The second example is similar to the first except this time we are including a PATITION for the row number.

SELECT
    ROW_NUMBER() OVER(PARTITION BY c.Name ORDER BY p.ListPrice DESC) AS RowNum,
    p.ProductNumber,
    p.Name,
    p.ListPrice,
    c.Name AS CategoryName
FROM SalesLT.Product p
INNER JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID

As mentioned earlier the partition restarts the numbering for the fields specified in the partition clause. The results of this query will be similar to the previous example, the only difference is the numbering will be restarted for each change in ProductCategory.Name (c.Name) column. The order of the rows within the partition cannot be garanteed as there are multiple rows with the same ListPrice value.

RowNum. ProductNumber. Name...................... ListPrice. CategoryName......
1       SB-M891-S      Men's Bib-Shorts, S        89.99      Bib-Shorts
2       SB-M891-M      Men's Bib-Shorts, M        89.99      Bib-Shorts
3       SB-M891-L      Men's Bib-Shorts, L        89.99      Bib-Shorts
1       RA-H123        Hitch Rack - 4-Bike        120.00     Bike Racks
1       ST-1401        All-Purpose Bike Stand     159.00     Bike Stands
1       BC-M005        Mountain Bottle Cage       9.99       Bottles and Cages
2       BC-R205        Road Bottle Cage           8.99       Bottles and Cages
3       WB-H098        Water Bottle - 30 oz.      4.99       Bottles and Cages
1       BB-9108        HL Bottom Bracket          121.49     Bottom Brackets
2       BB-8107        ML Bottom Bracket          101.24     Bottom Brackets

Example 3. Selecting a range of rows
This final example demonstrates how we can use the row numbering to select a specific range of rows based on the assigned number.
The WITH statement creates a result set containing the numbered rows. Then a Select statement queries for the rows between a certain range, in this case 21 and 30

WITH ProductDetail AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY p.ListPrice DESC) AS RowNum,
        p.ProductNumber,
        p.Name AS ProductName,
        p.ListPrice,
        c.Name AS CategoryName
    FROM SalesLT.Product p
    INNER JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID
)
SELECT
    RowNum,
    ProductNumber,
    ProductName,
    ListPrice,
    CategoryName
FROM ProductDetail WHERE rownum BETWEEN 21 AND 30

Here are the queried rows. A partition has not been specified so all the

RowNum. ProductNumber. ProductName............... ListPrice. CategoryName.
21      BK-R89B-58     Road-250 Black, 58         2443.35    Road Bikes
22      BK-T79Y-46     Touring-1000 Yellow, 46    2384.07    Touring Bikes
23      BK-T79Y-50     Touring-1000 Yellow, 50    2384.07    Touring Bikes
24      BK-T79Y-54     Touring-1000 Yellow, 54    2384.07    Touring Bikes
25      BK-T79Y-60     Touring-1000 Yellow, 60    2384.07    Touring Bikes
26      BK-T79U-46     Touring-1000 Blue, 46      2384.07    Touring Bikes
27      BK-T79U-50     Touring-1000 Blue, 50      2384.07    Touring Bikes
28      BK-T79U-54     Touring-1000 Blue, 54      2384.07    Touring Bikes
29      BK-T79U-60     Touring-1000 Blue, 60      2384.07    Touring Bikes
30      BK-M68S-38     Mountain-200 Silver, 38    2319.99    Mountain Bikes

What Next

There are many benefits to understanding row numbering. I recently used a query (similar to example 3) to remove duplicate data in a table by numbering the rows, partitioned on certain fields, and deleting rows with a number greater than 1. I have also used row numbering to return specific ranges of rows for a paged grid. This implementation gave an added complication where we had to sort the rows based on which grid column the user selected.

The rest is now up to you……

Quick Introduction to Page WebMethods

DaveLeave a comment

PageMethods can be implemented by the following steps.

1. Include the Services namespace.

using System.Web.Services;

2. Add a public static method to your code behind page and decorate with [WebMethod].

[WebMethod]
public static string GetSalutation()
{
    var salutation = string.Empty;
    var hour = DateTime.Now.TimeOfDay.Hours;
    if (hour >= 0 && hour <= 11)
    {
         salutation = "Good Morning!";
    }
    else if (hour >= 12 && hour <= 16)
    {
        salutation = "Good Afternoon!";
    }
    else
    {
        salutation = "Good Evening!";
    }
     return salutation;
}

3. Add ScriptManager control from the Ajax Extensions tab on the Visual Studio toolbox.

4. Enable page methods in your app by adding EnablePageMethods=”true” to the Scriptmanager markup.

5. Write javascript to call the pagemethod and handle the result.

function GetGreeting()
{
    PageMethods.GetSalutation(onSucess, onError);

    function onSucess(result)
    {
        var ctrl = document.getElementById("Greeting");
        ctrl.value = result;
    }

    function onError(result)
    {
        alert('Cannot process your request at the moment, please try later.');
    }
}

6. Create an interface with elements for invoking the javascript method and displaying the results;

<input onclick="GetGreeting()" type="button" value="Go" />
<input id="Greeting" type="text" value="" />

Possible Issues

1. The scriptmanager must be placed inside the pages Form tags, it it is not an exception will be thrown;

‘ScriptManager’ must be placed inside a form tag with runat=server

2. If you miss off enabling the pagemethods on the scriptmanager a javascript error will be thrown;

‘PageMethods’ is undefined

Final Note

Interacting with page controls must be done in the javascript side as page controls cannot be accessed from with a pagemethod. So any values you need from page controls will need to be passed to the page methods as parameters.

I have added a visual studio project containing a couple of examples including the one above to Github.