getting records x through y from a select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

getting records x through y from a select

Author

Topic

cord
Starting Member

7 Posts

Posted - 2004-06-13 : 02:49:15

I have been searching the web for the past day or so in order to find something in mssql that is comparable to the LIMIT clause in mysql. In otherwords, I need to return only results x through y from a certain select query.
The search I have gone through has mostly yielded complicated stored procedues and queries whose performace is questionable.

I cannot fathom why Sql Server does not have some mechanism to do this.

Any help would be HIGHLY appreciated. Currently I am using something like:
select top 40 * from mytable where primKey NOT IN (select top 20 primKey from mytable)

primKey is obviously indexed. It is an identity colum of integer, starts from 1 and increments by 1.

Thanks again

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-13 : 10:52:20

You need an order by clause

select top 40 * from (select top 20 * from tbl order by primkey) a order by prmkey desc.
or maybe
select t1.*
from tbl t1
join
(select top 40 primkey from (select top 20 primkey from tbl order by primkey) a order by prmkey desc
) t2
on t1.primkey = t2.primkey

If you could make primkey sequential (an identity isn't) then you could use a between.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cord
Starting Member

7 Posts

Posted - 2004-06-13 : 14:58:13

hmm. I dont understand what you mean by having primkey sequential then.

Also, the problem is, that I may need to sort results using other columns. For example, sometimes I use the following query:

select top 40 * from mytable where primKey NOT IN (select top 20 primKey from mytable order by FirstName) order by FirstName

Would this query structure be a problem when the number of records increases? Potentially, I am looking at tens of thousands of records.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-13 : 20:21:16

As long as the column you are ordering by is indexed.
best would be FirstName, primKey for this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources