Selecting a block of records - SQL Server Forums

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)

Selecting a block of records

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-06 : 15:17:05

How can you select a block of records using SQL? For example, I have 5000 records in a table and I want to select the records between 100 and 150. How can I do such a thing?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-06 : 15:39:47

Search SQL Team for "TOP" and you'll get a bunch of articles on using TOP to limit rows; however graz's article "What's after TOP?" is probably the best:

http://www.sqlteam.com/item.asp?ItemID=566

developer
Starting Member

1 Post

Posted - 2002-01-07 : 03:29:52

use pubs
Select * from publishers
where pub_id not in ( Select Top 3 pub_id from publishers) And pub_id in (Select Top 6 pub_id from publishers)

Nazim
A custom title

1408 Posts

Posted - 2002-01-07 : 04:06:22

i think you should follow the link Rob provided of Graz Article. its neatly done.

Follow this link too, thought might be it will help you.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11716

----------------------------
Anything that Doesn't Kills you Makes you Stronger

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-08 : 08:04:02

Thanks a lot for the links and info.

Okay, I tried this code :

use Northwind
Select ProductID from Products
Where ProductID not in (Select Top 3 ProductID from Products)
And
ProductID in (Select Top 10 ProductID from Products)

The results :

ProductID
-----------
4
5
65
66
6
7
8

Can ayone please enlighten me why there are "65" and "66" in results?

Nazim
A custom title

1408 Posts

Posted - 2002-01-08 : 08:31:04

coz u didnt sorted your data .

use Northwind
Select ProductID from Products
Where ProductID not in (Select Top 3 ProductID from Products order by productid)
And
ProductID in (Select Top 10 ProductID from Products order by product id)

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

spock
Starting Member

35 Posts

Posted - 2002-01-08 : 08:32:43

when you order by ProductID in the subqueries then 65,66 will not appear. you might have inserted 66,65 before you inserted 7,8 i guess so when u list from top they appear.you also might not have a clustered index on that column.

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-08 : 11:27:02

quote:
you also might not have a clustered index on that column.

why not?

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-08 : 11:37:04

quote:

quote:
you also might not have a clustered index on that column.

why not?

Assuming that Product ID is the primary key, there will be an index of either clustered or nonclustered on it. The default would be clustered, but you can specify a nonclustered index (most likely at the time of table design) if the clustered would be better used elsewhere on the table.

Justin

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-08 : 12:18:51

In theory, it's irrelevant whether there's an clustered index on the table. Your subqueries with TOPs in them don't have ORDER BY so the set of rows they return is arbitrary.

In practice, it will normally be implemented by a scan through the table or a covering index. If the table/index is not fragmented so the pages are in order in the database, and the database file is not fragmented, this will yield the rows in clustered index order.

However, Northwind's Product table does have ProductID as its clustered index, so what's happening here? Let's simplify it: just take the subquery SELECT TOP 10 ProductID FROM Products. You still get 65 and 66. But look at the query plan: it's not using the clustered index, it's using the SupplierProducts index -- remember, indexes identify the row in the table by its clustered index value[2] -- and product IDs 65 and 66 are supplied by suppliers near the start of this index.

[1] Even just SELECT ProductID FROM Products will use this index. Why? Because there are more index rows on a page than table rows on a page so there will be fewer accesses to get the same data.

[2] Heaps, tables without clustered indexes have a row id that is accessible only to SQL Server's internals. Tables with non-unique clustered indexes have an extra hidden 4-byte field to 'uniqueify' the clustered index value.

Edited by - Arnold Fribble on 01/08/2002 12:20:04

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-08 : 15:43:00

Arnold, I couldn't understand why it's using SupplierProducts instead of primary key ProductID. Can you explaint it a bit more? I don't know much about the inner workings of SQL Server..

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-09 : 12:35:14

Because ProductID is the clustered index of Products, each entry in the SuppliersProducts index contains the ProductID of the row it is referencing in the table.

Since each entry in the SupplierProducts index only contains SupplierID and ProductID, there are more entries on each page of the index than there are rows per page of the Products table. So that's fewer reads to get all the ProductID values for the Products table (although there's not much difference for a table this size).

If the query had wanted the ProductID values in ascending order, SQL Server would have used the clustered index, since that's quicker than scanning the SuppliersProducts index into a temporary and sorting it.

Compare:
SELECT ProductID FROM Products ORDER BY ProductID
SELECT ProductID FROM Products WITH (INDEX(SuppliersProducts)) ORDER BY ProductID

Edited by - Arnold Fribble on 01/09/2002 16:19:37

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-09 : 17:37:17

Thanks a lot for the info Arnold. I understood it.

Now, I tried the code to a bit more dynamic. Since I want to retrieve the top number as parameter this was necessary. This is the code I tried:

use Northwind
declare @a int,@b int,@s1 varchar(99)
set @a=3
set @b=4
set @s1='select ProductID
from Products
where ProductID not in
(Select Top '+convert(varchar, @a)+' ProductID from Products order by ProductID desc)
and
ProductID in
(Select Top '+convert(varchar, @b)+' ProductID from Products order by ProductID desc)'

exec(@s1)

The result:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'o'.

When I make the @s1 single line it gives the following error instead:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'b'.

uhm..Why dou you think this could be?

Edited by - kensai on 01/09/2002 17:39:39

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-09 : 17:45:28

Hi

The problem is @s1 is only 99 characters long. Your statement is longer than this so it's getting truncated.

Try making @s1 a varchar(1000)

Damian

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-10 : 17:01:22

ah ah ah thanks a lot Merkin!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources