to get the nth record meeting the query requirement

[Michelle]

In SQL server, if I want to retrieve the first record which meets the query,
I can use 'select top 1'. However if I want to query the nth record, is
there a easy way to do it?
Thanks.

USE pubs
SELECT TOP 1 pub_id
FROM titles
WHERE type ='business'
ORDER BY pub_id

[Stu]

Would something like this work? replace n with the required record.

select TOP 1 pub_id from (

> SELECT TOP n pub_id
> FROM titles
> WHERE type ='business'
> ORDER BY pub_id

)
ORDER BY pub_id desc

Cheers
Stu

[Anith Sen]

If you meant the nth largest/smallest value in the dataset, you can try :

SELECT pub_id
FROM titles
WHERE type = 'business'
AND (SELECT COUNT(t1.pub_id)
FROM titles t1
WHERE t1.type = titles.type
AND t1.pubid <= titles.pubid) = @n

If you need to handles ties, you have to use COUNT(DISTINCT pub_id) in your
subquery.

--
- Anith
( Please reply to newsgroups only )

[Vishal Parkar]

Try this:

USE pubs
declare @x int
set @x=7 --get 7th record on the basis of order of pub_id
select rank, pub_id from
(SELECT (select count(distinct pub_id) from titles where pub_id <= a.pub_id)
rank, pub_id
FROM titles a
WHERE type ='business') X
where rank=@x


-Vishal

"Michelle" <Michelle@yahoo.com> wrote in message
news:ONo1AJxPDHA.1336@TK2MSFTNGP11.phx.gbl...

> In SQL server, if I want to retrieve the first record which meets the

query,

> I can use 'select top 1'. However if I want to query the nth record, is
> there a easy way to do it?
> Thanks.
>
> USE pubs
> SELECT TOP 1 pub_id
> FROM titles
> WHERE type ='business'
> ORDER BY pub_id
>
>
>

[Vishal Parkar]

USE pubs
SELECT TOP 1 pub_id
FROM titles
WHERE type ='business'
ORDER BY pub_id
DESC --changed the clause to DESC to get the last record.

-Vishal


"Michelle" <Michelle@yahoo.com> wrote in message
news:#evxGMxPDHA.1336@TK2MSFTNGP11.phx.gbl...

> or how can I retrieve the last record meeting the query? Thanks for your
> help.
>
> "Michelle" <Michelle@yahoo.com> wrote in message
> news:ONo1AJxPDHA.1336@TK2MSFTNGP11.phx.gbl...

> > In SQL server, if I want to retrieve the first record which meets the

> query,

> > I can use 'select top 1'. However if I want to query the nth record, is
> > there a easy way to do it?
> > Thanks.
> >
> > USE pubs
> > SELECT TOP 1 pub_id
> > FROM titles
> > WHERE type ='business'
> > ORDER BY pub_id
> >
> >
> >

>
>