Re: fetch the nth record in an oracle table

From: Jay Weiland <jay_at_pixar.com>
Date: Tue, 11 Dec 2001 11:50:51 -0800
Message-ID: <3C16639B.1AEB7B88@pixar.com>

Same concept, but a little cleaner in terms of SQL is a two table join

select soda_name
from jay_test
order by soda_name

SODA_NAME

---

Coke
Diet Coke
Diet Pepsi
Pepsi
Sprite

select j1.soda_name, count(*)
from jay_test j1, jay_test j2
where j1.soda_name >= j2.soda_name
group by j1.soda_name;

SODA_NAME COUNT(*)

-------------------- ---------
Coke                         1
Diet Coke                    2
Diet Pepsi                   3
Pepsi                        4
Sprite                       5

And just add a "having count(*) = [nth]" and it will pull out that record. If the values are not unique, you can nest the results in subqueries, but the basic concept remains the same.

Jay!!!

TurkBear wrote:

> If you must:
>
> select * from (select * from mytable order by something) where rownum < ( 1 number greater than the one you want to see )
> MINUS
> select * from (select * from mytable order by samething) where rownum < ( number that you want to see )
>
> Note that both order by clauses MUST be the same ....
>
> "Marc Leman" <marc.leman_at_cgey.NOSPAMcom> wrote:
>
> >I mean I would like to see only the nth line that my request - select * from
> >mytable order by something - would retrieve.
> >
> >
> >
> >"rob" <rob_at_dsdelft.nl> a écrit dans le message de news:
> >9v59pf$a0o$1_at_news.tudelft.nl...
> >> what's your definition of 'the nth record'?
> >> in a relational database records are stored in a non-specific order
> >>
> >> Rob
> >>
> >> "dibule" <dibule_at_dibulehome.fr> wrote in message
> >> news:9v574l$bm6$1_at_s1.read.news.oleane.net...
> >> > Does someone know how to catch the nth record in an Oracle table?
> >> >
> >> > Thx
> >> >
> >> >
> >>
> >>
> >
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> Check out our new Unlimited Server. No Download or Time Limits!
> -----== Over 80,000 Newsgroups - 19 Different Servers! ==-----
Received on Tue Dec 11 2001 - 13:50:51 CST