Don Croata (el*******@gmail.com) writes:
The idea behind is creating a stored procedure that retrieves a
numbered list of elements. This list could be displayed in the web, so
it needs to have that "rownum":
SELECT rn, el
FROM (
SELECT <<rownum>> AS rn, element AS el
FROM table
) AS tmp
I've seen some approaches using rank=count(*), but they look ugly.
Could I use something like a temporal sequence in SQL Server?
There is a row_number() function in SQL 2005, currently in beta.
Beware that this is not a row_number of Oracle fame, but one
related to the actual result set. The syntax is also a little more
complicated. If memory serves, this is derived from the SQL-99 standard.
For SQL-2000 the best bet is probably to say:
CREATE TABLE #t (row_number int IDENTITY,
col1 ....)
INSERT #t (col1, )
SELECT col1, ...
FROM ...
ORDER BY
Note that you cannot use SELECT INTO for this, as it's not guaranteed
that the identity value will follow the ORDER BY clause in this case.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp