SQL - Add a Row Number to a Select Number

Sometimes when selcting data from a table it might be useful to have a row count as well.
To achieve this use the ROW_NUMBER() function.
The ROW_NUMBER() function is used in conguction with the OVER keyword and requires an order by. If your OVER section only has a ORDER BY clause the data will not be divided and the row numbers will start at 1 and increment to the number of rows you have in your result set.
If you would like to have the row count start over every time a certain piece of data changes you can use PARTITION BY in your OVER clause.


First let's build a table variable and populate some data in to it and then we can break apart the ROW_NUMBER() function.


 
--declare a table variable
DECLARE @Client_Order AS
TABLE
    (
    client_id INT
    ,order_id INT
    ,amount FLOAT
    ,orderDate DATETIME
    );


--populate the table with some data   
INSERT
INTO @client_order
(
    client_id
    ,order_id
    ,amount
    ,orderDate
)
(
SELECT 1,    1,    25.00,    '2010-06-01'
UNION ALL
SELECT 2,    2,    50.00,    '2010-06-01'
UNION ALL
SELECT 3,    3,    75.00,    '2010-06-02'
UNION ALL
SELECT 1,    4,    33.33,    '2010-06-03'
UNION ALL
SELECT 4,    5,    25.00,    '2010-06-03'
UNION ALL
SELECT 1,    6,    2.00,        '2010-06-04'
UNION ALL
SELECT 5,    7,    5.00,        '2010-06-05'
UNION ALL
SELECT 5,    8,    200.00,    '2010-06-06'
UNION ALL
SELECT 2,    9,    20.25,    '2010-06-07'
UNION ALL
SELECT 6,    10,    125.25,    '2010-06-08'
);


In the first example we will select all the data and have the rows start at 0 and continue until the last row.


 
--select data from the table with a row number for the whole table   
SELECT
    client_id
    , order_id
    , amount
    , orderDate
    ,ROW_NUMBER() OVER (ORDER BY orderDate) AS 'row'
FROM
    @client_order
ORDER
BY
    row



This is a normal SELECT statement for the most part but you can see that one of the columns we are selecting has the ROW_NUMBER function.
ROW_NUMBER is followed by OVER and then another set of parenteses that hold an order by clause. The Order by tells the ROW_NUMBER what order the rows should be counted in. In this example we have ordered the rows by OrderDate. We have also given the ROW_NUMBER column an alis of 'row'.
The row number corresponds to the order_id in this example but you can put a different ORDER BY clause in the OVER statement and have a totally different ROW_NUMBER returned.


In our second example we will be partitioning the data by the client and return a row number for each client in the order they were placed.

 
--select the data from the table with a row number for each client_id

SELECT
    client_id
    , order_id
    , amount
    , orderdate
    ,ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY orderdate) AS 'row'
FROM
    @client_order
ORDER
BY
    client_id
    , row;

You can see that we have add a PARTITION BY clause in the OVER statement. This tells ROW_NUMBER when to start the row count back to 1 again. Every time the client_id changes the row number starts over. We have ordered the entire query by Client_id so we can see all the orders a client made one after the other and by 'row' so we can see which number order this for that specific client. This is like building a client_order_id column from the fields that exist in the database already.

 
This is a very useful function and can be used in a ton of different applications.