[SQL] - Adding Row/Serial Number to Any Database Query

[ganeshmoorthy]

This query will add Serial or Row Numbers 1,2,3...(ascending order) in the result set of any database

SELECT CustCode, CustName, 
(SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode <= C.CustCode) AS SrNo
FROM Customers C
ORDER BY CustCode

This will add numbers ....3,2,1 (decending order)

[SELECT CustCode, CustName, 
(SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode >= C.CustCode) AS SrNo
FROM Customers C
ORDER BY CustCode

[abu_arqam]

You can use :-

SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1) As SrNo,ColumnName1,ColumnName2,ColumnName3 from Tablename

Thanks

[vinodachu]

Thank U

[wsoomro]

Thanks Seems Interesting.

[alMubarmij]

This query will add Serial or Row Numbers 1,2,3...(ascending order) in the result set of any database

SELECT CustCode, CustName, 
(SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode <= C.CustCode) AS SrNo
FROM Customers C
ORDER BY CustCode

This will add numbers ....3,2,1 (decending order)

[SELECT CustCode, CustName, 
(SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode >= C.CustCode) AS SrNo
FROM Customers C
ORDER BY CustCode

Can it work with Access 2003 or SQL Server 2000 ?