Select Top, Bottom...is there a middle? 3

[Ovatvvon]

Hi all,

As you know, we can select the top or bottom set number of records by specifying something like...

Select Top 25 * From someDBname

Is there something like mid or middle where we can specify what record to start at, and how many records to retrieve after that?

For instance, if a query would return 100 records, and we want records 20 through 30, is there something where we can say...

Select Mid(20,10) * From DBname

-Ovatvvon :-Q

 

[gmmastros]

What version of SQL Server?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CaptainD]

This work

select top 10 Lname from tblPersonnel where LName Not IN(SELECT top 10 lName from tblPersonnel order by lname) order by lname

 

[Ovatvvon]

Hi George,

It's SQL 2005


CaptainD,

That might work if we change inside query limit at each point, but I'm hoping there may be an inherient feature that will provide better performance than the "Not In". If there is no other solution though, that may be the only alternative.



-Ovatvvon :-Q

 

[gmmastros]

Read this...

http://www.codeproject.com/KB/database/PagingResults.aspx

If that doesn't help, let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatriciaObreja]

If you want to select the records from 20 to 30:

select top 10 * from TableName
where id not in (select top 19 id from TableName order by id) order by id

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[Ovatvvon]

Both of those solutions worked great.

Thanks!


-Ovatvvon :-Q