select Nth row from table
Posted on 2002-05-10
I have a table with a billion rows
id (primary key, identity), email, firstname, etc...
I want to grab a specific row number, but the id's have gaps (from deleting rows)
my first attempt:
select top 1 * from mytable e where (select count(id) from mytable where id <= e.id) = 412
that would return the 412th row regardless of gaps in id.. the problem with that query is its sooo slow!
can anyone help me with a better solution?
maybe a better idea would be to re-index the table whenever a row is deleted but I could not figure out how to do that either (without making a temporary table) BTW: no temp tables.. the table is toooo BIG to be duplicated