Best way to do a SELECT TOP n TO m

[Will Clark]

Hi there,

I was wondering if there was a better way to do a SELECT where it returns
not the TOP 5 (or whatever) but, say, the TOP 6 to 8?

What I've currently got is...

SELECT TOP 3 * FROM table WHERE
id NOT IN (SELECT TOP 5 id FROM table ORDER BY id)
ORDER BY id

While this is ok for this (simple) query, it can get quite messy if the
query is more complex. There must be a better way of doing it, mustn't
there?

Thanks for any help :o)

Will

[Greg D. Moore \(Strider\)]

"Will Clark" <re****************************@noone-lives-here.com> wrote in
message news:bo**********@sparta.btinternet.com...

Hi there,

I was wondering if there was a better way to do a SELECT where it returns
not the TOP 5 (or whatever) but, say, the TOP 6 to 8?

What I've currently got is...

SELECT TOP 3 * FROM table WHERE
id NOT IN (SELECT TOP 5 id FROM table ORDER BY id)
ORDER BY id

While this is ok for this (simple) query, it can get quite messy if the
query is more complex. There must be a better way of doing it, mustn't
there?
That's generally along the lines of what most folks do.


Thanks for any help :o)

Will

[Will Clark]

> That's generally along the lines of what most folks do.

Thats a shame - I was hoping that there would be a way which didn't involve
running effectively the same SELECT twice on the table...

Oh, well, thanks all the same :o)

[Anith Sen]

Do:

SELECT *
FROM tbl
WHERE (SELECT COUNT(*)
FROM tbl t1
WHERE t1.col <= tbl.col) BETWEEN 6 AND 8 ;

--
-- Anith
( Please reply to newsgroups only )

[Daniel Buchholz]

Will Clark wrote:

That's generally along the lines of what most folks do.

Thats a shame - I was hoping that there would be a way which didn't involve
running effectively the same SELECT twice on the table...

Hey Will!

I need that a lot for web frontend grid paging.

What I do is: First select only the PKs in question into a temp table or
table variable (with own PK) and make sure that sort order and
contraints are right.

Then select the required rows from the temp table and join to the
"production table" to get the fields you need.

Some example (northwind):

DECLARE @page int
DECLARE @pagesize int
SET @page = 3
SET @pagesize = 10

CREATE TABLE #pager (
OrderID int,
IDpager int identity(1,1)
)

INSERT INTO #pager (OrderID)
SELECT OrderID
FROM Orders
ORDER BY Freight DESC

SELECT IDPager, ShipName, ShipAddress, ShipCity
FROM Orders
INNER JOIN #pager ON Orders.OrderID = #pager.ORderID
WHERE IDPager BETWEEN ((@page-1)*@pagesize + 1) AND (@page*@pagesize)

DROP TABLE #pager
Daniel

[Will Clark]

Thanks for all your help, Greg, Daniel and Anith - its been really useful,
and I've got lots to play around with now :o)