SQL Row Number

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

Hi

I want to know if it is possible to retrieve a row no. for each row of a SQL
Statement, row no below is not a part of the Table, but needs to be
generated by the SQL Statement ???

Row Name State City
1 JJJ CA YYy
2 XXX VA Ayyaya
3 aaa OH Anabab

TIA
Barry
 
As Dave and Carl have indicated you can use the ROW_NUMBER() function in SQL
Server 2005. For prior versions of SQL Server you can use two techniques:

1). Using a temp table to generate a row number. I will make the assumption
your primary key for the table is PK_ID and the table is named MyTable.

-- Create the temp table to host the new row number
CREATE TABLE #MyRowNumber (
RowNumber int IDENTITY (1, 1),
PK_ID char(15) )

-- Generate the row number
-- you can sort by any field that makes sense or leave it sorted by the PK
INSERT #MyRowNumber (PK_ID)
SELECT PK_ID
FROM MyTable
ORDER BY Name

-- Select the row number
SELECT RowNumber, mt.PK_ID, Name, State, City
FROM #MyRowNumber mr
INNER JOIN MyTable mt
ON mr.PK_ID = mt.PK_ID
ORDER BY RowNumber

2). Using a query based on count of PKs:

SELECT PK_ID, Name, State, City,
(SELECT COUNT(*) FROM MyTable mt2 WHERE mt2.PK_ID <= mt.PK_ID) AS RowNumber
FROM MyTable mt
ORDER BY PK_ID

Regards,

Plamen Ratchev
http://www.SQLStudio.com
 
Back
Top