Getting row position in a select

I need to get the row position in a select.
SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
How can i get the current row number in the result set like this?
MYFIELD1 MYFIELD2 ROW_POSITION
myfield1value1, myfield2value1, 1
myfield1value2, myfield2value2, 2
myfield1value3, myfield2value3, 3
...
Thanksexamnotes (checcouno@.discussions.microsoft.com) writes:
> I need to get the row position in a select.
> SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
> How can i get the current row number in the result set like this?
> MYFIELD1 MYFIELD2 ROW_POSITION
> myfield1value1, myfield2value1, 1
> myfield1value2, myfield2value2, 2
> myfield1value3, myfield2value3, 3
> ...
In SQL 2000, the best is to create a temp table with an identity column
and insert to that one. The IDENTITY column, will give you the value
you need.
If there is a unique key in the result set, you can also do something
like:
SELECT keycol, col1, col2,
rowno = (SELECT COUNT(*) FROM tbl t2
WHERE t1.keycol <= t2.keycol)
FROM tbl t1
But for large data sets, the performance may not be fantastic.
In SQL 2005 it's a lot easier, as it comes with a row_number() function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
SELECT
OT.*,
(SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
OT.pcolumn ) As row_no
FROM YOUR_TABLE OT
OT is Outer Table Allies and IT is Inner Table Allies
I think that it will help you.
"checcouno" wrote:

> I need to get the row position in a select.
> SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
> How can i get the current row number in the result set like this?
> MYFIELD1 MYFIELD2 ROW_POSITION
> myfield1value1, myfield2value1, 1
> myfield1value2, myfield2value2, 2
> myfield1value3, myfield2value3, 3
> ...
>
> Thanks
>|||Hi
Read this thread
http://www.aspfaq.com/show.asp?id=2427
Regards
R.D
"Akbar khan is a Senior Database develope" wrote:
> Hi
> SELECT
> OT.*,
> (SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
> OT.pcolumn ) As row_no
> FROM YOUR_TABLE OT
> OT is Outer Table Allies and IT is Inner Table Allies
> I think that it will help you.
>
> "checcouno" wrote:
>|||It works only with order by pcolumn!
"Akbar khan is a Senior Database develope" wrote:
> Hi
> SELECT
> OT.*,
> (SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
> OT.pcolumn ) As row_no
> FROM YOUR_TABLE OT
> OT is Outer Table Allies and IT is Inner Table Allies
> I think that it will help you.
>
> "checcouno" wrote:
>sql