Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coderdude
Starting Member
24 Posts |
 Posted - 2004-08-19 : 15:22:25
|
| I am trying to write a SELECT statement that would return a column that is essentially equal to the row number of reach row returned. For example:RowNum FName LName1 Sue Bradley2 John Doe3 Mary JaneIt would basically work like an identity column on the table, however, I want to be able to specify a different sort order on my select and still have the RowNum column be numbered sequentially...As you can see the above example was ordered by last name. Upon ordering by first name I would expect these results:RowNum FName LName1 John Doe2 Mary Jane3 Sue BradleyThe only method I've been able to discover is to create a temp table with an identity field and select the data into that table in the desired order. However, I'm hoping to avoid using temporary tables. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-08-19 : 15:50:51
|
| do this at the presentation layer. why does the recordset need row numbers? what are you doing with these numbers? if it is just to display on the screen or on a page, do it at the presentation side, not the database side.- Jeff |
 |
|
|
coderdude
Starting Member
24 Posts |
Posted - 2004-08-19 : 15:56:37
|
| Because I don't need the numbers on the presentation layer... What I'm trying to do is simply select a certain number of records for paging. Say, for example, I have a table with 100 rows, but I only want 10 rows starting at row 30 in last name order.As I said before, I'm using a temp table. I'm ploping all 100 in last name order into a temp table with an auto increment. Then I'll select all rows with an id > 30 and < 40. Works perfectly fine, even on tables with LOTS of rows, however, it seems quite wasteful to insert it all into a temp table just to get a row number... I was hoping for a quicker way. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-08-19 : 16:18:47
|
| Oh, you need help with PAGING! just say so !search around here and elsewhere for T-SQL paging ideas ... I have a solution in my weblogs but I am still not entirely sure if it is a good one (http://weblogs.sqlteam.com/jeffs) . My algorithm, in theory, should process as few rows as possible for paging, w/o using a temp table. be sure to read the comments for other ideas.If you are using a temp table, you can use the SET ROWCOUNT to ensure that (in your example) you only insert 40 rows into the temp table. that is, to return rows X through Y, when you build the temp table, set the rowcount to Y first. that will help a little.- Jeff |
|
|
|
|
|
|
|
|
Select Sequential Numbers with Data