query to fetch n'th row of a table

Question

Post reply

query to fetch n'th row of a table

sathish802004

SSC Veteran

Points: 278
More actions
February 12, 2008 at 2:45 am

#186744

ALTER procedure SP_FETCH_ROWS(@num int)
AS
BEGIN
select * from employee a
where @num =(select count(*) from employee b where a.id >= b.id)
END
This is a procedure which i got from a site for fetching the nth row of a table. Can any one explain me how the procedure works ?
Thanks,
Sathish Kumar A

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI There,

Haev you tried running that code?

I ran it on a table of 428577 rows for the N = 5 and it ran for 5minutes and then I cancelled the query!

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

sathish802004 SSC Veteran Points: 278 More actions · Answer 2

No, i havent tried for such case.... my only concern is that i want to know how tht query works ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It works rather badly. There's a triangular join in there, with a correlated subquery. The combination will be hell for large rowcounts

Jeff wrote a nice article on the dangers of the triangular join[/url]

There's a much better way on SQL2005 using the row_number

select * from

(select *, row_number() Over (order by ID) AS RowNo FROM Employees) Emp

where RowNo = @Num

Edit: fixing my syntax

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Hi,

The above solution is missing the order by in the over clause:

select * from

(select *, row_number() over(ORDER BY ID) AS RowNo FROM Employee) Emp

where RowNo = @Num

I also did some testnig on Statistics time:

You query tested on my table of 428578 rows.

search row 5 = 2ms

search row 100 = 3ms

search row 1000 = 4ms

search row 10000 = 14ms

search row 100000 = 112ms

I test this using a CTE and here are the result

WITH Result (ID,Num) as

(

select ID

,row_number() over(ORDER BY ID) as num

from Employee a with (nolock)

)

SELECT *

FROM Result

WHERE Num = @Num

search row 5 = 2ms

search row 100 = 4ms

search row 1000 = 4ms

search row 10000 = 7ms

search row 100000 = 48ms

CPU Times also seem to be lower on the CTE.

Looks like there maybe more than one method.

Still 100% which is best from time the CTE looks like it maybe faster.