| selecting particular row (merged) [message #265561] |
Thu, 06 September 2007 11:40  |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
hi friends
I have some doubts .please answer.
1.
when ever i try to use rownum function other than < or <= it gives no row selected like
select * from emp where rownum=2;
select * from emp where rownum>=3;
no row selected while emp hav 14 rows..
******************************************************
2...
I use oracle 9i version. i face follwing error for my this query
select rate_per_sqft from apartment where rate_per_sqft in ( select avg_rate_per_sqft from region1 where rownum<=3 order by avg_rate_per_sqft desc )
My sub query should calculate the top 3 avg_rate_per_sqft from region,,, but its giving following error
ERROR at line 1:
ORA-00907: missing right parenthesis.
i cannot use top method in 9i . please tell the alternate solution too
[Updated on: Thu, 06 September 2007 11:49] Report message to a moderator |
|
|
|
|
|
|
|
| Re: pdoubts in sql oracle 9i [message #265572 is a reply to message #265561] |
Thu, 06 September 2007 11:55  |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
| Quote: |
select * from emp where rownum=2;
|
You may use this as follows
select * from
(
select rownum rn,col1,col2,..,coln from emp
)
where rn = 2
|
|
|
|
| Re: pdoubts in sql oracle 9i [message #265574 is a reply to message #265568] |
Thu, 06 September 2007 11:57 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
| anacedent wrote on Thu, 06 September 2007 11:49 | >select * from emp where rownum=2;
The problem with rownum=2 is as follows:
1) The a row is obtained so ROWNUM = 1 but that does not qualify for the WHERE clause & the row is discarded
2) Goto #1
3) Repeat until no more rows are available
|
thanks a lot for answering ... from ur point 1 i can only understand this that rownum 1 will be discarded but why not 2 is shown
and why rownum> does not work
plz answer the second question also of using order by in subquery
Thanks alot
Ashish
|
|
|
|
| Re: pdoubts in sql oracle 9i [message #265576 is a reply to message #265572] |
Thu, 06 September 2007 11:59 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
| muzahidul islam wrote on Thu, 06 September 2007 11:55 | | Quote: |
select * from emp where rownum=2;
|
You may use this as follows
select * from
(
select rownum rn,col1,col2,..,coln from emp
)
where rn = 2
|
its not working i just saw ...
|
|
|
|
|
|
| Re: pdoubts in sql oracle 9i [message #265584 is a reply to message #265561] |
Thu, 06 September 2007 12:10 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| ashish2345 wrote on Thu, 06 September 2007 12:40 |
select * from emp where rownum=2;
select * from emp where rownum>=3;
no row selected while emp hav 14 rows..
|
Do a search on ROWNUM. In effect,
select * from emp where rownum <=1;
select * from emp where rownum <=12; [Updated on: Thu, 06 September 2007 12:10] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: selecting particular row [message #265607 is a reply to message #265605] |
Thu, 06 September 2007 13:00 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
| anacedent wrote on Thu, 06 September 2007 12:57 | Your question is totally & 100% nonsensical.
SQL returns a collection of records.
A table is analogous to having a basket of balls.
Which is the 2nd ball in the basket?
|
understand . see if i have table result with marks and
student name and i want to get the student having second highest marks ... how to do in that case order by desc and selection of second row required..
reply
regards
ashish
[mod-edit] illiterate IM speak removed once again
[Updated on: Thu, 06 September 2007 14:42] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
