Home » SQL & PL/SQL » SQL & PL/SQL » sql help
sql help [message #232383] Fri, 20 April 2007 06:32 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi,


I have created one table called branch, I have insterted the 5 records , i want to find out 3rd inserted record. How to findout 3rd inserted record.


regards
Srinivas
Re: sql help [message #232385 is a reply to message #232383] Fri, 20 April 2007 06:40 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Can't guess what your table or data look's like....

So my suggestion is to
write a select statment with a where clause..... Rolling Eyes

SELECT
Re: sql help [message #232386 is a reply to message #232383] Fri, 20 April 2007 06:42 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
There is nothing called as first row, third row .. last row in databases. Only if you have inserted rows using a sequence or timestamp then you can order them and consider as first row or third row etc.
Re: sql help [message #232389 is a reply to message #232383] Fri, 20 April 2007 06:48 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
try some thing like that

select * from (select sal,rownum rownumber from emp )  where rownumber=3


--Yash
Re: sql help [message #232393 is a reply to message #232389] Fri, 20 April 2007 06:56 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Bonker,

i saw that same response in lot of earlier topics ..

i have created one table like that...

create table tmp (i number ,name varchar(30))
insert into tmp values (1,'aa')
insert into tmp values (2,'bb')
insert into tmp values (5,'ee')
insert into tmp values (3,'cc')
insert into tmp values (4,'dd')

select i,name,rownum from tmp
1,aa ,1
2,bb ,2
5,ee ,3
3,cc ,4
4,dd ,5

select statement return the row in same order which order i have inserted ..so why we can not get the first or last row..

Might be i am wrong..plz correct me


--Yash
Re: sql help [message #232395 is a reply to message #232389] Fri, 20 April 2007 06:59 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
asume that emp table

it has 14 records already.

iam inserted 10 records.

i want to find out 3 rd insert record ?

how to find out? what base we are found.......... any sql tricks

thanks
srinivas
Re: sql help [message #232397 is a reply to message #232393] Fri, 20 April 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle don't know, record and care about the order in which rows are inserted.
So there is no first, second... unless you defined this order maybe adding a field.

Regards
Michel

Re: sql help [message #232404 is a reply to message #232395] Fri, 20 April 2007 07:07 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:


i saw that same response in lot of earlier topics ..
....
....

select statement return the row in same order which order i have inserted ..so why we can not get the first or last row..

Might be i am wrong..plz correct me





Read this and be enlightened.


http://asktom.oracle.com/pls/asktom/f?p=100:11:3030183311198644::::P11_QUESTION_ID:912210644860

Re: sql help [message #232432 is a reply to message #232397] Fri, 20 April 2007 08:45 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Fri, 20 April 2007 08:00
Oracle don't know, record and care about the order in which rows are inserted.
So there is no first, second... unless you defined this order maybe adding a field.

Regards
Michel




Michel, don't waste your breath on this clown. He will not believe you anyway. Have you seen his other posts?
Re: sql help [message #232438 is a reply to message #232432] Fri, 20 April 2007 09:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
3 rd insert row .... have you seen his other posts


Yeah, it fracking UNBELIEVABLE.

He has posted hundreds of messages in the last month, where 95% his "problems" are because he just doesn't GET it that there is no "Nth" row in a RDBMS.

I can't imagine how he is able to keep a database related job longer than about 2 hours. Wink

Might be i am wrong..plz correct me 


We have tried for month, pavuluri. There is no hope, because you are not listening.

Re: sql help [message #232439 is a reply to message #232432] Fri, 20 April 2007 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Joy_division, you may be right.
I surely read and answered some of his questions but I rarely read who post only what is posted.

Regards
Michel
Re: sql help [message #232441 is a reply to message #232393] Fri, 20 April 2007 09:41 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yash,

Here's a complete exemple, in 8i (you can reproduce in any release), where you get a different order each time.
dbms_rowid is only there to show you that you get rows in neither id order nor rowid order nor insert order:
SQL> create tablespace test 
  2  datafile 'E:\Oracle\Bases\MIK8\TESTS_01.DBF' size 10m,
  3           'E:\Oracle\Bases\MIK8\TESTS_02.DBF' size 10m
  4  default storage (initial 20k next 20k pctincrease 0);

Tablespace created.

SQL> create table test (id number, val char(2000)) parallel 3 tablespace test;

Table created.

SQL> alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_02.DBF');

Table altered.

SQL> alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_01.DBF');

Table altered.

SQL> alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_01.DBF');

Table altered.

SQL> alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_02.DBF');

Table altered.

SQL> desc test
 Name          Null?    Type
 ------------- -------- -------------------
 ID                     NUMBER
 VAL                    CHAR(2000)

SQL> begin
  2    for i in reverse 0..25 loop
  3      insert into test values (i, chr(ascii('A')+i));
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select extent_id, file_id, block_id, blocks from dba_extents 
  2  where owner=user and segment_name='TEST';
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          7          2          5
         1          7          7          5
         2          6          2          5
         3          6          7          5
         4          7         12          5
         5          6         12          5

6 rows selected.

SQL> select id, 
  2         dbms_rowid.rowid_relative_fno(rowid) file#,
  3         dbms_rowid.rowid_block_number(rowid) block#,
  4         dbms_rowid.rowid_row_number(rowid) row#
  5  from test
  6  /
        ID      FILE#     BLOCK#       ROW#
---------- ---------- ---------- ----------
        16          6          2          0
        14          6          4          0
        13          6          5          0
        22          7          6          0
        20          7          8          0
        10          6          8          0
        19          7          9          0
         7          6         11          0
         1          6         12          0
         0          6         13          0
         3          7         15          0
        25          7          3          0
        24          7          4          0
        23          7          5          0
        21          7          7          0
        18          7         10          0
         6          7         12          0
         5          7         13          0
         4          7         14          0
        15          6          3          0
        12          6          6          0
        11          6          7          0
         9          6          9          0
         8          6         10          0
        17          7         11          0
         2          7         16          0

26 rows selected.

SQL> /
        ID      FILE#     BLOCK#       ROW#
---------- ---------- ---------- ----------
        16          6          2          0
        14          6          4          0
        13          6          5          0
        21          7          7          0
         8          6         10          0
        17          7         11          0
         0          6         13          0
         2          7         16          0
        15          6          3          0
        12          6          6          0
        11          6          7          0
         9          6          9          0
        18          7         10          0
         6          7         12          0
         4          7         14          0
         3          7         15          0
        25          7          3          0
        24          7          4          0
        23          7          5          0
        22          7          6          0
        20          7          8          0
        10          6          8          0
        19          7          9          0
         7          6         11          0
         1          6         12          0
         5          7         13          0

26 rows selected.

SQL> /
        ID      FILE#     BLOCK#       ROW#
---------- ---------- ---------- ----------
        25          7          3          0
        24          7          4          0
        23          7          5          0
        20          7          8          0
         1          6         12          0
         0          6         13          0
        16          6          2          0
        13          6          5          0
        22          7          6          0
        21          7          7          0
         9          6          9          0
         7          6         11          0
        18          7         10          0
         6          7         12          0
         4          7         14          0
        15          6          3          0
        14          6          4          0
        12          6          6          0
        11          6          7          0
        10          6          8          0
         8          6         10          0
        19          7          9          0
        17          7         11          0
         5          7         13          0
         3          7         15          0
         2          7         16          0

26 rows selected.


I once posted another exemple with a partitioned table in Jonathan Lewis blog (http://jonathanlewis.wordpress.com/all-postings/).

Regards
Michel
Previous Topic: Job Scheduling Made easy in 10g - DBMS_SCHEDULER -- DBMS_JOB++++++++
Next Topic: DDL in function
Goto Forum:
  


Current Time: Mon Dec 05 09:09:20 CST 2016

Total time taken to generate the page: 0.08228 seconds