| Select only one row [message #259] |
Fri, 01 February 2002 08:36  |
Aravind
Messages: 41
Registered: January 2002
|
Member |
|
|
Hi,
I have a table from which I want to select only the first row. Is there any way to do this??
Thanks in advance..
Aravind
|
|
|
|
|
|
| Re: Select only one row [message #268 is a reply to message #259] |
Sat, 02 February 2002 07:02  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
In Oracle there is nothing which defines what is the first, second or last row. You can not compare an Oracle table to an excel sheet. If you realy want the first row you have to define how you order them (what is the first row for you?).
ROWNUM gives you the order in which the query returns you the rows. So there is no garanty (even if it seems to work) that the row whit ROWNUM=1 is the first row which has been inserted.
If you realy want the rows orderd by insertion date you need a date field with the insertion date.
HTH
Mike
|
|
|
|
| Re: Select only one row [message #270 is a reply to message #259] |
Sat, 02 February 2002 08:41 |
sokeh
Messages: 77
Registered: August 2000
|
Member |
|
|
Mike,
I agree with you to a certain extent.
As long as you are not ordering your rows, then rownum returns the current row number correctly because rownum value is assigned to a row before a sort is done.
As a result, the only time rownum can't be trusted is if you are sorting the rows.
First instance, while this query, select ename from emp
where ronum < 3
will return the first two rows, because it is sorted, won't necessarily be first two rows in the entire sort order.
In the absence of the example, rownum will correctly return the indicated row number in a query.
|
|
|
|
| Re: Select only one row [message #279 is a reply to message #259] |
Sun, 03 February 2002 07:03 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
I can only repeat what I said in my first reply.
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle returns the row from a table.
ROWNUM is not a stored value and doesn't give you any information in which order they have be inserted or where they are phisycaly stored. It only informes in which order the query returns the rows.
So when you realy want to get only the first row, you have to define of what (which order) it should be the first row. So, yes you are right, if it is the first row of the query you want you can us ROWNUM.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
