Home » Other » Test » Ram Nainar
Ram Nainar [message #244594] Wed, 13 June 2007 08:22 Go to next message
oracleram
Messages: 23
Registered: November 2006
Junior Member
How do i retrieve first 10 rows & last 10 rows of a table?Thanx in advance...

Re: Ram Nainar [message #244597 is a reply to message #244594] Wed, 13 June 2007 08:32 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Welcome,

I suppose this is a test message but in case it is not: search the board. A small hint though: Oracle has no knowledge of first nor last.

MHE
Re: Ram Nainar [message #244624 is a reply to message #244594] Wed, 13 June 2007 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before posting a question be assured that you:
- always post your Oracle version (4 decimals).
- read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

Regards
Michel

Re: Ram Nainar [message #254905 is a reply to message #244594] Mon, 30 July 2007 01:05 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

select * from test1 where rownum <5;

It is possible by using rownum.
Re: Ram Nainar [message #254918 is a reply to message #254905] Mon, 30 July 2007 01:23 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Arju wrote on Mon, 30 July 2007 08:05
select * from test1 where rownum <5;

It is possible by using rownum.

This will return 4 totally random rows. Can return four different rows tomorrow than it does today.
Search the forums for top-n, look in the FAQ, plenty of places to read.
Re: Ram Nainar [message #254925 is a reply to message #244594] Mon, 30 July 2007 01:34 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

But in my pc it always give result in the way as I input it. Here it is.
SQL> create table test3(a number);

Table created.

SQL> insert into test3 values(5);

1 row created.

SQL> insert into test3 values(1);

1 row created.

SQL> insert into test3 values(2);

1 row created.

SQL> insert into test3 values(100);

1 row created.

SQL> insert into test3 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3 where rownum<2;

         A
----------
         5

SQL> select * from test3 where rownum<3;

         A
----------
         5
         1

SQL> select * from test3 where rownum<4;

         A
----------
         5
         1
         2
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ sqlplus arju/arju

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 30 12:31:57 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from test3 where rownum<4;

         A
----------
         5
         1
         2

[Updated on: Mon, 30 July 2007 01:34]

Report message to a moderator

Re: Ram Nainar [message #254929 is a reply to message #254918] Mon, 30 July 2007 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is also a question posted 1.5 month ago.

Regards
Michel
Re: Ram Nainar [message #254932 is a reply to message #254925] Mon, 30 July 2007 01:51 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Arju
But in my pc it always give result in the way as I input it.

It is because your statistical pattern is inadequate.
Re: Ram Nainar [message #254934 is a reply to message #254925] Mon, 30 July 2007 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju,

This has been said and said again, the only way to get an order is to give an ORDER BY clause.

You get the same rows in the same order by accident you can't rely on this.

Regards
Michel
Re: Ram Nainar [message #254942 is a reply to message #254934] Mon, 30 July 2007 02:09 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
SQL> create table faq (id number, my_filler varchar2(4000));

Table created.

SQL> insert all
  2  into faq values (1, lpad('x', 4000, 'x'))
  3  into faq values (2, lpad('x', 4000, 'x'))
  4  into faq values (3, lpad('x', 4000, 'x'))
  5  into faq values (4, lpad('x', 4000, 'x'))
  6  into faq values (5, lpad('x', 4000, 'x'))
  7  into faq values (6, lpad('x', 4000, 'x'))
  8  into faq values (7, lpad('x', 4000, 'x'))
  9  into faq values (8, lpad('x', 4000, 'x'))
 10  into faq values (9, lpad('x', 4000, 'x'))
 11  into faq values (10, lpad('x', 4000, 'x'))
 12  select * from dual;

10 rows created.

SQL>
SQL> select id
  2  from   faq
  3  where  rownum < 5;

        ID
----------
         1
         2
         3
         4

SQL>
SQL> delete faq where id between 2 and 6;

5 rows deleted.

SQL> insert into faq values (11, lpad('x', 4000, 'x'));

1 row created.

SQL> select id
  2  from   faq
  3  where  rownum < 5;

        ID
----------
         1
        11
         7
         8


Now, you still believe you get the records back in the order you inserted them?

Print this post and keep it. Read it every night before going to sleep.
Re: Ram Nainar [message #254947 is a reply to message #254942] Mon, 30 July 2007 02:16 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
[code]
Print this post and keep it. Read it every night before going to sleep.

Isn't too much.
Previous Topic: Hi
Next Topic: Segment Space Management
Goto Forum:
  


Current Time: Fri Mar 29 10:05:18 CDT 2024