-
Feb 16th, 2007, 12:48 AM
#1
[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
Last edited by ganeshmoorthy; Feb 28th, 2007 at 07:20 AM.
If an answer to your question has been helpful, then please, Rate it!
Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.
-
Mar 24th, 2010, 04:06 AM
#2
New Member
Re: [SQL] - Adding Row/Serial Number to Any Database Query
You can use :-
SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1) As SrNo,ColumnName1,ColumnName2,ColumnName3 from Tablename
Thanks
-
Jun 27th, 2011, 06:44 AM
#3
New Member
Re: [SQL] - Adding Row/Serial Number to Any Database Query
-
Oct 28th, 2011, 01:52 PM
#4
New Member
Re: [SQL] - Adding Row/Serial Number to Any Database Query
Thanks Seems Interesting.
-
Feb 1st, 2012, 03:36 AM
#5
Member
Re: [SQL] - Adding Row/Serial Number to Any Database Query
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 ?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|