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.

SQL - Formatting and comparing dates

When comparing dates it is important to remember that the DATETIME format has a timestamp that is included in the comparison.

So if you are comparing two dates and the timestamp does not match the comparison will be evaluated as false.

For example:  '2010-06-15 15:10:32.248' does not equal '2010-06-15 12:32:59.398'

To get around this you can change the timestamp value in your select query so it is 00:00:00.000.

Do this by using two CASTs and a FLOOR.

The first(inner) CAST will change the DATETIME to a FLOAT.  This keeps the values of the date and time but puts it in a format(FLOAT) to manipulate using the FLOOR.  The FLOOR rounds the time down to 00:00:00.000.

Now we need to CAST it back into a DATETIME and we are done.

The syntax for this query looks like this:

SELECT 

        CAST(

                FLOOR(

                        CAST(GETDATE() AS FLOAT)

                        )AS DATETIME

                )

If you would like to format the date so it doesn't have a timestamp at all you can CONVERT it into a VARCHAR.  I know people will say that this is the job of the front end but sometimes you need to pull these values into a report in SQL Server Management Studio and your result set is the front end, so to speak.  If you copy the set into Excel it can easily be formatted in Excel as well but if it is a query you run a lot you can write the query to do the conversion for you and save yourself the time in Excel.

The syntax to CONVERT the DATETIME into a VARCHAR looks like this:

SELECT 

        CONVERT(VARCHAR(10),GETDATE(),101) 

Here is a screen shot of these two queries at work: