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:

Combine 2 Columns in Excel Using CONCATENATE Function

In Microsoft Excel you can take two columns and combine the values into one column.



In my example video you can see there are two columns, Phone and Type, that contain some info. If you want to combine the into one column you take the following steps.

1. Create a new column somewhere in your spreadsheet
   
2. Put the cursor in the first field of the column you created
   
3. Make sure you are on the HOME tab and click on the pull down arrow next to the AUTO SUM button
   
4. Select MORE FUNCTIONS and search for Concatenate
   
5. With your cursor In the first value field click on the first cell you would like to combine
   
6. Put your cursor in the second value field and type a space (this is so your values have a space in between them when they are combined)
   
7. Put your cursor in the third value field and click on the second cell you would like to combine
   
8. Click OK
   
9. If you would like to have this formula applied to the entire column hover over the bottom right hand corner of the cell with the formula in it so you see a dark black cross and double click.
   
10. You are done unless you would like to remove the old columns but keep the Concatenated values (if you do, continue to steps 11 through 13)
    
11. Select all the fields that have a formula in it and COPY the contents
    
12. Paste special the contents back in to the same location and choose Values. This will replace the formulas with Values.
    
13. Remove the original columns and save your document

Find How Many Times a Specific Character Is Repeated In a String

Recently I came across an email address in the database at work that had 2 '@' symbols (The offending email address looked something like this: 'name.last@@aol.com'.)  Clearly the front end was not doing it's job and we had bad data.  I was curious to see if there were any other email addresses that had more than one @ so I wrote the below query.

SELECT

     Client_id

     ,Email

FROM

     Client

WHERE

     LEN(Email) - LEN(REPLACE(Email, '@', '')) > 1

Now let's break it down:

LEN returns the number of characters in the field.

LEN('name.last@@aol.com') returns 18 which is the number of characters in the string.


REPLACE('name.last@@aol.com', '@', '') returns the string without the '@' symbol.  The first argument is the string you want to search ('name.last@@aol.com').  The second argument is the character you are searching for ('@').  The third argument is what to replace the '@' with ('') in this case nothing (empty string).  This will just take the '@' out of the string and return the string without it.


LEN(REPLACE('name.last@@aol.com', '@', '')) then returns the number of characters in the string after you replace the '@' with an empty string.  


If you take the LEN of the full string and subtract the LEN of the string after you replace the character you are trying to count you are left with the number of times the search character is in the string. 


Compare this to 1 in the WHERE clause and you have all Client_ids and their Emails where the email has 2 @'s.


You can use this for other things as well, but this is how I used it today.