Home » SQL & PL/SQL » SQL & PL/SQL » selecting particular row (merged)
selecting particular row (merged) [message #265561] Thu, 06 September 2007 11:40 Go to next message
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 #265564 is a reply to message #265561] Thu, 06 September 2007 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: pdoubts in sql oracle 9i [message #265568 is a reply to message #265561] Thu, 06 September 2007 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>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
Re: pdoubts in sql oracle 9i [message #265572 is a reply to message #265561] Thu, 06 September 2007 11:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #265583 is a reply to message #265561] Thu, 06 September 2007 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What I posted is correct & how Oracle/SQL processes the rows in the table.
If EVERY row obtained results in ROWNUM=1, then no row qualifies for ROWNUM=2; same is true for ROWNUM>1

THINK about it.
SQL does NOT behave as you expect, but it is what it is!
Re: pdoubts in sql oracle 9i [message #265584 is a reply to message #265561] Thu, 06 September 2007 12:10 Go to previous messageGo to next message
joy_division
Messages: 4643
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;
would be same as your first query
select * from emp where rownum <=12;
would be the same as your second query.

[Updated on: Thu, 06 September 2007 12:10]

Report message to a moderator

Re: pdoubts in sql oracle 9i [message #265585 is a reply to message #265583] Thu, 06 September 2007 12:10 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
okay thanks answer second question too
regards
Ashish

[mod-edit] illiterate IM speak words removed.

[Updated on: Thu, 06 September 2007 12:11] by Moderator

Report message to a moderator

selecting particular row [message #265602 is a reply to message #265561] Thu, 06 September 2007 12:50 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Hi

now rownum=2 does not work. how cab i get only second rowdata
oracle 9i
ashish
Re: selecting particular row [message #265604 is a reply to message #265602] Thu, 06 September 2007 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't start a new topic for the same question.

Regards
Michel
Re: selecting particular row [message #265605 is a reply to message #265602] Thu, 06 September 2007 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
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?

Re: selecting particular row [message #265607 is a reply to message #265605] Thu, 06 September 2007 13:00 Go to previous messageGo to next message
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

Re: selecting particular row [message #265610 is a reply to message #265607] Thu, 06 September 2007 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
[edit: wrong quote]

select * 
from (select rownum rn, * 
      from  (select ... order by ...)
      where rownum <= 2)
where rn = 2
/

Regards
Michel

[Updated on: Thu, 06 September 2007 13:24]

Report message to a moderator

Re: selecting particular row (merged) [message #265613 is a reply to message #265561] Thu, 06 September 2007 13:36 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Use rank function
select * from 
(
select employee_name, salary, 
rank() OVER (ORDER BY salary desc) rn
from employees
) where rn = 2
Re: selecting particular row (merged) [message #265700 is a reply to message #265613] Fri, 07 September 2007 01:33 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
About rownum: old thread.

MHE
Previous Topic: document on TYPE
Next Topic: identifying gaps (merged)
Goto Forum:
  


Current Time: Thu Dec 08 18:13:16 CST 2016

Total time taken to generate the page: 0.08840 seconds