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: 
 

city team rank ------------ ------------ ---- Boston Bruins 1 Boston Celtics 2 Boston Red Sox 3 Chicago Bears 1 Chicago Black Hawks 2 Chicago Cubs 3 Chicago White Sox 4 New England Patriots 1 New York Giants 1 New York Islanders 2 New York Jets 3 New York Knicks 4 New York Mets 5 New York Rangers 6 New York Yankees 7

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