finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
Why is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
David Portas
sql