What is the position of a row ?
The simple answer is there isn't
one.
If you insert 1000 rows into a table and then want to get back the 43rd row
you inserted you can't. You can read through all rows and return the 43rd one
that the database reads from the table but that is not the same thing.
Why is it so?
Relational databases are all about the data in the tables. The only guarantee
is that data, the order of the data being inserted is not a physical piece of
data and so you can't access it.
If this was possible what would you expect? Imagine a situation where you
have 100 users inserting data, what order would you expect the data to be
returned. The order it was inserted you answer. But when was the data inserted?
When the insert statement is called or when the transaction commits, its not
simple is it. Therefore its down to you to store the data which you can
use to define the order in which you want to return the data
So how do you get round this? Well you need to store the orderof
insert in the table. This can be done in a number of ways
Identity
Column
create table myTable
(id
int identity(1,1)
,someOtherColumn ....
)
With an identity column the value of the column is generated sequentially for
each row inserted. With an identity column you can then query based on this
column to get the Nth row inserted.
select
from
myTable
where id
= @NthRow
Date
Column
The second option is to put a data column on the table. This way you can
order the data in the order it was inserted and then return the Nth row.
create table myTable
(pk etc...
,InsertedDate datetime
,someOtherColumn ....
)
In SQL 2005 you can use the ROW_NUMBER() function and in SQL 2000 you can use
the TOP feature. (In SQL 2000 If want the row to be dynamic you need to use
dynamic SQL)
If you need the 1099th row then in SQL 2000 use
select top 1 <some columns>
from
(
select top 1099 insertsedDate
, <some columns>
from myTable
order by
insertsedDate asc)topData
order
by insertsedDate desc
and in SQL 2005 use
select <some columns>
from
(
select row_number()
over (order by
insertsedDate) row
, <some columns>
from myTable
order by
insertsedDate asc)topData
where
row=1099
-