Fetching rows using top/bottom.

[shuklix]

Hi Every one,

I have a Table which has.

Id , Name, Email

I have to fetch 10 records every time, and have links to previous/next on all the page, and the sorting is done
not on Id but on Name/Email field.

I get my 1st 10 records by sorting on Name,
How do I get the next 10 records.

I can not do some thing like

select id name, email from Client id > 10
order by name.

I can do some thing like

Select Bottom 10 from (select top 20 from
Client order by name)

I think I can use the BottomCount Function but I can not figure out the syntax.


TIA,
Saurabh

 

[JamesLean]

You can use something like this:

SELECT TOP 10 id, name, email
FROM client
WHERE name NOT IN (
  SELECT TOP 10 name
  FROM client
  ORDER BY name
)
ORDER BY name

Obviously, if you want the 3rd set of ten you can change the TOP n statement in the subquery to TOP 20, etc.

--James

 

[shuklix]

I just happened to read this post.

http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=8785

It says Top takes a lot of time. MY Table has
huge number of records.

Thanks a lot for your suggestion James.

Saurabh

 

[JamesLean]

I would have to disagree with whoever wrote that post. Usually TOP and SET ROWCOUNT will perform quite equally, however there are times that TOP will actually be more efficient.

Microsoft recommend you use TOP rather than SET ROWCOUNT.

--James

 

[shuklix]

I am using a SP.

select top 5 id,name, email from Client works.

but

select top @offset id, name, email from Client does
not compile.

any thoughts ?

 

[JamesLean]

Unfortunately you can't use a variable with TOP. You have to use dynamic SQL for that:

DECLARE @sql

SET @sql = 'SELECT TOP ' + CAST(@offset AS varchar(10)) + ' id, name, email FROM client'

EXEC(@sql)

--James

 

[sunila7]

Hi,

Other way of doing it would be, without using a dynamic SQL is...

Declare @@offset int
Set @@offset = 5
Set ROWCOUNT @@Offset
select id, name, email from Client
SET ROWCOUNT 0 -- Reset so that all rows.


Sunil