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

Hi Barry,

Using the ROW_NUMBER() function will do exactly that in T-SQL:

SQL Server 2005 Books Online
ROW_NUMBER (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

Show quoteHide quote

Dave Sexton wrote:
But, just to be clear, that's SQL 2005 only - the function is not availabel
in SQL 2000 or earlier.

-cd

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


Show quoteHide quote

difference between a .dll and .ocx????
2.0 Generics - Feature or Bug?
HttpListener BeginGetContext does not seem to handle more than 2 request simultaneously
Framework 3.0 Installation Problem (99% CPU Utilization)
Debug or Release ?