[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

enumerate records

sali

3/21/2007 4:30:00 PM

sql2000
is there some clever way to enumerate records in ordered query, so the
enumeration reflects the order of records in query?

one option is to use cursor to traverse the result set, but is too slow

easy example could be, f.e.:

set @recno_new = @recno_last +1

executed for each row, but it is hard [imposisible] to implement. that
approach needs some global [or static] variable, but there is no such type.

any comment?

thnx.
3 Answers

Rafael Lenartowicz

3/21/2007 5:15:00 PM

0

I'm not sure if 2000 supports it, but in 2005 it's very simple:

SELECT ROW_NUMBER ( ) OVER(ORDER BY your_order_column) ,
other_columns_you_want_to_select FROM tablename WHERE... etc.





"sali" <sali@euroherc.hr> wrote in message
news:OsY7IY9aHHA.4012@TK2MSFTNGP03.phx.gbl...
> sql2000
> is there some clever way to enumerate records in ordered query, so the
> enumeration reflects the order of records in query?
>
> one option is to use cursor to traverse the result set, but is too slow
>
> easy example could be, f.e.:
>
> set @recno_new = @recno_last +1
>
> executed for each row, but it is hard [imposisible] to implement. that
> approach needs some global [or static] variable, but there is no such
> type.
>
> any comment?
>
> thnx.
>
>
>
>
>


Plamen Ratchev

3/21/2007 5:57:00 PM

0

Two popular ways to do it in SQL 2000 are using an identity column with a
temp table and using a subquery to count number of rows. Essentially in the
first method you create a temp table with identity column and insert your
data in the desired order. The identity column becomes your row number. The
second method works fine if you have an unique column that you will be
ordering by. Here is an example with employees using the employee id to
order and get row number:

SELECT EmployeeID, EmployeeName,
(SELECT COUNT(*) FROM Employees AS E2
WHERE E2.EmployeeID <= E1.EmployeeID) AS RowNumber
FROM Employees AS E1
ORDER BY EmployeeID

You can see detailed information on the topic in the following KB article:

How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com...

HTH,

Plamen Ratchev
http://www.SQL...

sali

3/22/2007 8:02:00 AM

0

"Plamen Ratchev" <Plamen@SQLStudio.com> je napisao u poruci interesnoj
grupi:evJITK%23aHHA.4140@TK2MSFTNGP06.phx.gbl...
> Two popular ways to do it in SQL 2000 are using an identity column with a
> temp table and using a subquery to count number of rows. Essentially in
> the first method you create a temp table with identity column and insert
> your data in the desired order. The identity column becomes your row
> number. The second method works fine if you have an unique column that you
> will be ordering by. Here is an example with employees using the employee
> id to order and get row number:
>
> SELECT EmployeeID, EmployeeName,
> (SELECT COUNT(*) FROM Employees AS E2
> WHERE E2.EmployeeID <= E1.EmployeeID) AS RowNumber
> FROM Employees AS E1
> ORDER BY EmployeeID
>
> You can see detailed information on the topic in the following KB article:
>
> How to dynamically number rows in a SELECT Transact-SQL statement
> http://support.microsoft.com...
>
> HTH,
>
> Plamen Ratchev
> http://www.SQL...
>


thnx for all sugestions, they helped me.
i needed not only the simple sequence number, but the multi-seqment sequence
number.

it is little bit funy that such a simple task as generating rowset number
[in ordered query] required such a huge resource consumption [required for
self-join], or using intermediate queries.

i found, for years, that the expert's opinion for years was that you have
bad data design if you even think about rowset number?
then, suddenly sql2005 changed the point.