Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

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


  2. #2
    New Member
    Join Date
    Mar 2010
    Posts
    1

    Resolved 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

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    1

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

    Thank U

  4. #4
    New Member
    Join Date
    Oct 2011
    Posts
    1

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

    Thanks Seems Interesting.

  5. #5
    Member
    Join Date
    May 2009
    Posts
    55

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

    Quote Originally Posted by ganeshmoorthy View Post
    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
  •  



Click Here to Expand Forum to Full Width