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

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

Code:

---

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)

Code:

---

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

---

You can use :-

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

Thanks :thumb:

Thank U

Thanks Seems Interesting.

Quote:

---

Originally Posted by ganeshmoorthy

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

Code:

---

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)

Code:

---

[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 ?