Me.Thoughts.ToString()

I encountered some TSQL Select problem where I want my result set to have a “number” on the first column. If for example you have a simple select statement

USE Northwind
SELECT CustomerID
, CompanyName
FROM Customers
ORDER BY CustomerID

which outputs

CustomerID CompanyName
---------- ----------------------------------------
ALFKI Alfreds Futterkiste
ANATR Ana Trujillo Emparedados y helados
ANTON Antonio Moreno Taquería
AROUT Around the Horn
BERGS Berglunds snabbköp

… so on …

I want it to have a number on its first column like

Rank CustomerID CompanyName
---- ---------- ----------------------------------------
1 ALFKI Alfreds Futterkiste
2 ANATR Ana Trujillo Emparedados y helados
3 ANTON Antonio Moreno Taquería
4 AROUT Around the Horn
5 BERGS Berglunds snabbköp

… so on …

How can I achieve this? I google on the topic and came across the dilemma on how will I construct the search string on that problem. I tried different variations like “how to add row number on my TSQL select statement” to “how will i add a row count column on my select statement”. After a couple of minutes of searching and browsing I came across this article from MSDN but find their solution cumbersome. They have one easy solution but unfortunately, it only works on SQL Server 2005 which can be done in to two ways, by using the Row_Number() and the Rank() Function.

Example:

-- using Row_Number()

SELECT Row_Number() OVER (order by CustomerID asc) as RowNumber
, CustomerName
FROM Customers

-- using Rank() [almost the same as row_number]

SELECT Rank() OVER (order by CustomerID asc) as RowNumber
, CustomerName
FROM Customers

Then I came across this forum thread which basically gives me the idea on how to solve the problem. The trick is to have your SELECT statement create a new table and using the IDENTITY() function to simultaneously create the rowID of your table after which SELECT the data of the new table which now contains an additional “row number” column.

SELECT RowNum = IDENTITY(int, 1, 1)
, Name = CompanyName
INTO #Customers
FROM Customers
ORDDER BY CompanyName

SELECT *
FROM #Customers

In my example, I dumped my data into a temporary table since I will have no use of it after I get my result so that the table will be deleted after my session.