//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: How do I return row numbers with my query?


How do I return row numbers with my query?

Often, people want to "invent" an identity, or rank, on the fly. So their original result set would look like this: 
 
Lastname Firstname 
-------- --------- 
Evans    Bob 
Smith    Frank
 
And they would want this: 
 
Rownum Lastname Firstname 
------ -------- --------- 
1      Evans    Bob 
2      Smith    Frank
 
This would act like Oracle's ROWNUM, which isn't supported in SQL Server. 
 
Of course, once you've retrieved this resultset into your ASP page, you could use a counter to increment as you're processing. This is by the easiest way, e.g. 
 
<% 
    ' ... 
    set rs = conn.execute(sql) 
    counter = 0 
    do while not rs.eof 
        counter = counter + 1 
        response.write counter & " " 
        response.write rs(0) & "<br>" 
        rs.movenext 
    loop 
    ' ... 
%>
 
However, some people really, really, really want the row number to come back from the database. It's a little less efficient, but let's examine a few methods. Given this sample data: 
 
SET NOCOUNT ON 
 
CREATE TABLE people 

    firstName VARCHAR(32), 
    lastName VARCHAR(32) 

GO 
 
INSERT people VALUES('Aaron', 'Bertrand') 
INSERT people VALUES('Andy', 'Roddick') 
INSERT people VALUES('Steve', 'Yzerman') 
INSERT people VALUES('Steve', 'Vai') 
INSERT people VALUES('Joe', 'Schmoe')
 
The first method we'll try is a COUNT with a GROUP BY: 
 
SELECT 
    rank = COUNT(*), 
    a.firstName, 
    a.lastName 
FROM 
    people a  
    INNER JOIN people b 
    ON  
        a.lastname > b.lastname 
        OR 
        ( 
            a.lastName = b.lastName 
            AND 
            a.firstName >= b.firstName 
        ) 
GROUP BY 
    a.firstName, 
    a.lastName 
ORDER BY 
    rank
 
We can also try a COUNT as a subquery, which doesn't require GROUP BY (which means you could include other columns in the outer query). 
 
SELECT 
    rank = ( 
        SELECT COUNT(*)  
        FROM people b 
        WHERE  
        a.lastname > b.lastname 
        OR 
        ( 
            a.lastName = b.lastName 
            AND a.firstName >= b.firstName 
        ) 
    ), 
    a.firstName, 
    a.lastName 
FROM 
    people a 
ORDER BY 
    a.firstName, 
    a.lastName
 
Results in all cases: 
 
rank firstName lastName 
---- --------- -------- 
1    Aaron     Bertrand 
2    Andy      Roddick 
3    Joe       Schmoe 
4    Steve     Vai 
5    Steve     Yzerman
 
Note that if you have duplicates in your table, you will end up with something like this: 
 
1    Aaron     Bertrand 
3    Joe       Schmoe 
3    Joe       Schmoe
 
So, to avoid this, you might want to make sure that either (a) you avoid and remove duplicates (see Article #2431); or (b) if duplicates are allowed and make sense for your data model, that you have some other primary key or unique identifier. Then, you can make it a part of the query; for example: 
 
SET NOCOUNT ON 
 
CREATE TABLE people 

    peopleID INT IDENTITY(1,1) PRIMARY KEY, 
    firstName VARCHAR(32), 
    lastName VARCHAR(32) 

GO 
 
INSERT people VALUES('Aaron', 'Bertrand') 
INSERT people VALUES('Andy', 'Roddick') 
INSERT people VALUES('Steve', 'Yzerman') 
INSERT people VALUES('Steve', 'Yzerman') 
INSERT people VALUES('Steve', 'Vai') 
INSERT people VALUES('Joe', 'Schmoe') 
 
SELECT 
    rank = ( 
        SELECT COUNT(*) 
        FROM people b 
        WHERE a.lastName > b.lastName 
        OR 
        ( 
            a.lastname = b.lastname 
            AND a.firstName >= b.firstName 
        ) 
    ) - ( 
        SELECT COUNT(*) FROM 
        people b 
        WHERE a.lastName = b.lastName 
        AND a.firstName = b.firstName 
        AND a.peopleID < b.peopleID 
    ), 
    a.firstName, 
    a.lastName 
FROM 
    people a 
ORDER BY 
    a.lastName, 
    a.firstName
 
Results: 
 
rank firstName lastName 
---- --------- -------- 
1    Aaron     Bertrand 
2    Andy      Roddick 
3    Joe       Schmoe 
4    Steve     Vai 
5    Steve     Yzerman 
6    Steve     Yzerman
 
Grouping within groups 
 
Often, you'll want a more complex row number scheme, for example you might want to rank within groups of a hierarchy. Let's say we wanted to list sports teams, and assign "ranks" alphabetically, within each city: 
 
CREATE TABLE #teams 

    city VARCHAR(20), 
    team VARCHAR(20) 

 
SET NOCOUNT ON 
 
INSERT #teams SELECT 'Boston', 'Celtics' 
INSERT #teams SELECT 'Boston', 'Bruins' 
INSERT #teams SELECT 'Boston', 'Red Sox' 
INSERT #teams SELECT 'New York', 'Yankees' 
INSERT #teams SELECT 'New York', 'Mets' 
INSERT #teams SELECT 'New York', 'Knicks' 
INSERT #teams SELECT 'New York', 'Rangers' 
INSERT #teams SELECT 'New York', 'Islanders' 
INSERT #teams SELECT 'New York', 'Jets' 
INSERT #teams SELECT 'New York', 'Giants' 
INSERT #teams SELECT 'Chicago', 'Black Hawks' 
INSERT #teams SELECT 'Chicago', 'Cubs' 
INSERT #teams SELECT 'Chicago', 'White Sox' 
INSERT #teams SELECT 'Chicago', 'Bears' 
INSERT #teams SELECT 'New England', 'Patriots' 
 
SELECT city, team, rank =  

    SELECT COUNT(*) 
    FROM #teams t2 
    WHERE t2.city = t1.city 
    AND t2.team <= t1.team 

    FROM #teams t1 
    ORDER BY city, team 
 
DROP TABLE #teams
 
Results: 
 
cityteamrank
----------------------------
BostonBruins1
BostonCeltics2
BostonRed Sox3
ChicagoBears1
ChicagoBlack Hawks2
ChicagoCubs3
ChicagoWhite Sox4
New EnglandPatriots1
New YorkGiants1
New YorkIslanders2
New YorkJets3
New YorkKnicks4
New YorkMets5
New YorkRangers6
New YorkYankees7
 
Keep in mind that, since your presentation tool (Crystal Reports, ASP, PHP, what have you) is going to have to treat every row separately anyway, it makes sense to just retrieve the rows in the correct order, and let the application compare every row to see if this is a new city or not, and accordingly increment the count or start over. This will greatly reduce the amount of strain you're putting on the database. 
 
Be sure to read KB #186133 for Microsoft's official word, and this article for a variety of approaches from Greg Larsen.  
 
There is also a great article by Itzik Ben-Gan available for registered users of SQL Server Magazine's web site: 
Windows Article - Welcome 
 
And thanks to Hugo Kornelis for pointing out an error based on my choice of names! Since the first letter of all of the first names I chose was alphabetically lower than each corresponding last name, I hadn't noticed that the results could be skewed in some of the above queries, if you added a name like Jason Arnott. I believe I have corrected this, but have deferred re-populating the results above for now. Please let us know if you find any issues...

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

 

 


Created: 12/9/2002 | Last Updated: 8/24/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (160)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...