fetch the nth record in an oracle table 
Author Message
 fetch the nth record in an oracle table
Does someone know how to catch the nth record in an Oracle table?

Thx



Sat, 29 May 2004 22:58:17 GMT
 fetch the nth record in an oracle table

what's your definition of  'the nth record'?
in a relational database records are stored in a non-specific order

Rob


Quote:
> Does someone know how to catch the nth record in an Oracle table?

> Thx



Sat, 29 May 2004 23:46:55 GMT
 fetch the nth record in an oracle table
I mean I would like to see only the nth line that my request - select * from
mytable order by something - would retrieve.



Quote:
> what's your definition of  'the nth record'?
> in a relational database records are stored in a non-specific order

> Rob



> > Does someone know how to catch the nth record in an Oracle table?

> > Thx



Sun, 30 May 2004 00:00:11 GMT
 fetch the nth record in an oracle table

Why...The 'number' of a record in an Oracle database is essentially meaningless since the order in which records are stored (
rather than retrieved) in undetermined, AFAIK...

What exactly do you mean by the nth record?...

Quote:

>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!  ==-----


Sun, 30 May 2004 00:43:42 GMT
 fetch the nth record in an oracle table

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 ....

Quote:

>I mean I would like to see only the nth line that my request - select * from
>mytable order by something - would retrieve.



>> what's your definition of  'the nth record'?
>> in a relational database records are stored in a non-specific order

>> Rob



>> > 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!  ==-----


Sun, 30 May 2004 00:50:10 GMT
 fetch the nth record in an oracle table

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!!!

Quote:

> 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 ....


> >I mean I would like to see only the nth line that my request - select * from
> >mytable order by something - would retrieve.



> >> what's your definition of  'the nth record'?
> >> in a relational database records are stored in a non-specific order

> >> Rob



> >> > 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!  ==-----



Sun, 30 May 2004 03:50:51 GMT
 fetch the nth record in an oracle table
select * from
  (select *, rownum rn
    from
      (select * from mytable order by something)
  )
where rn between FIRST_ROW_TO_SHOW and LAST_ROW_TO_SHOW


Quote:
> I mean I would like to see only the nth line that my request - select *
from
> mytable order by something - would retrieve.



> > what's your definition of  'the nth record'?
> > in a relational database records are stored in a non-specific order

> > Rob



> > > Does someone know how to catch the nth record in an Oracle table?

> > > Thx



Sun, 30 May 2004 03:57:55 GMT
 fetch the nth record in an oracle table
Thanks for all your answers, this will help me a lot



Quote:
> I mean I would like to see only the nth line that my request - select *
from
> mytable order by something - would retrieve.



> > what's your definition of  'the nth record'?
> > in a relational database records are stored in a non-specific order

> > Rob



> > > Does someone know how to catch the nth record in an Oracle table?

> > > Thx



Sun, 30 May 2004 16:22:36 GMT
 fetch the nth record in an oracle table

Quote:

> Does someone know how to catch the nth record in an Oracle table?

Based on replies from the others, here are the summary:

-- Work in Oracle 8i
select * from
  (select x.*, rownum rn
    from
      (select * from table order by something) x
  )
where rn between FIRST_ROW_TO_SHOW and LAST_ROW_TO_SHOW
/

-- Work in Oracle 7.x and beyond
select   j1.soda_name, count(*)
from     jay_test j1, jay_test j2
where    j1.soda_name >=3D j2.soda_name
group by j1.soda_name
having count(*) between FIRST_ROW_TO_SHOW and LAST_ROW_TO_SHOW
/

-- Work in Oracle 8i
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 )
/



Mon, 31 May 2004 10:47:32 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Fetch record into a plsql table (record.column_1 into table(index_1))

2. nth record in a table

3. Fetch nth to mth row

4. taking Nth~Mth rows from Fetch result

5. Performance problem in fetching record from table containg 2 million record

6. returning m records beginning from the nth record

7. Finding the nth largest or nth min. value

8. can SQL fetch the last record in a table

9. Linked tables in Access not fetching enough records

10. TQuery fetches all records when updating any other table or query

11. to get the nth record meeting the query requirement

12. nth record in a dataset


 
Powered by phpBB® Forum Software