OraFAQ Forum: SQL & PL/SQL » select * from table where n =

Home » SQL & PL/SQL » SQL & PL/SQL » select * from table where n = (SQL Query)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select * from table where n = [message #520531]

Tue, 23 August 2011 05:22

s4sandeep
Messages: 21
Registered: August 2011
Location: Faridabad, India

Junior Member

Hi,

Could anyone please tell what is the use of using 'n' in a query type as mentioned below:

select * from scott.emp a where n =

thanks in advance for reply.

Regards
Sandeep Verma

[Updated on: Tue, 23 August 2011 08:01] by Moderator

Report message to a moderator

Re: select * from table where n = [message #520532 is a reply to message #520531]

Tue, 23 August 2011 05:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is just an invalid query.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: select * from table where n = [message #520533 is a reply to message #520532]

Tue, 23 August 2011 05:28

s4sandeep
Messages: 21
Registered: August 2011
Location: Faridabad, India

Junior Member

That was only a part of a query to find the third highest salary from salary table, I could not understand the logic behind this query, please help(The Complete query is):

select * from scott.emp a where 3 = ( select count(distinct(b.sal)) from scott.emp b where a.sal>=b.sal)

[Updated on: Tue, 23 August 2011 08:01] by Moderator

Report message to a moderator

Re: select * from table where n = [message #520536 is a reply to message #520533]

Tue, 23 August 2011 05:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_select_the_TOP_N_rows_from_a_table.3F

Which part don't you understand?
Study it from the right to the left.

Regards
Michel

Report message to a moderator

Re: select * from table where n = [message #520537 is a reply to message #520536]

Tue, 23 August 2011 05:41

s4sandeep
Messages: 21
Registered: August 2011
Location: Faridabad, India

Junior Member

That when do we use 'n =' because most usually we use column name in pace of n

[Updated on: Tue, 23 August 2011 08:01] by Moderator

Report message to a moderator

Re: select * from table where n = [message #520539 is a reply to message #520537]

Tue, 23 August 2011 05:44

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Do you understand what a top-n query is?

Report message to a moderator

Re: select * from table where n = [message #520540 is a reply to message #520537]

Tue, 23 August 2011 05:45

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Think it as:
(select count(distinct(b.sal)) from scott.emp b where a.sal>=b.sal) = 3
which is a syntax that was not allowed in older version where subqueries can be only on right part.

Regards
Michel

[Edit: typos]

[Updated on: Tue, 23 August 2011 05:52]

Report message to a moderator

Re: select * from table where n = [message #520664 is a reply to message #520539]

Tue, 23 August 2011 18:47

s4sandeep
Messages: 21
Registered: August 2011
Location: Faridabad, India

Junior Member

Hi Cookiemonster,

Your reply did it all help, thanks!!

Report message to a moderator

Re: select * from table where n = [message #520665 is a reply to message #520664]

Tue, 23 August 2011 19:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Your reply did it all help, thanks!!

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

12. If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

Report message to a moderator

Re: select * from table where n = [message #520666 is a reply to message #520665]

Tue, 23 August 2011 19:49

s4sandeep
Messages: 21
Registered: August 2011
Location: Faridabad, India

Junior Member

This concept is used when we have to find nth row (for example nth highest/lowest column value corresponding row), in example I mentioned below we used this query to find 3rd highest salary using subqry , so in short 'n=' gives nth row. Its also good if we read some detail about rownum,rowid,rank concept.

One can check at :
http://www.devx.com/getHelpOn/10MinuteSolution/16608/1954
and
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2853107469873.

Report message to a moderator

Re: select * from table where n = [message #520684 is a reply to message #520664]

Wed, 24 August 2011 01:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

s4sandeep wrote on Wed, 24 August 2011 01:47

Hi Cookiemonster,

Your reply did it all help, thanks!!

I wonder why I reply to you when Top-N is the link I posted you and you understand it is a top-n query only when cookiemonster said it. There is something I don't understand here.

Regards
Michel

Report message to a moderator

Re: select * from table where n = [message #520816 is a reply to message #520684]

Wed, 24 August 2011 18:34

s4sandeep
Messages: 21
Registered: August 2011
Location: Faridabad, India

Junior Member

OOOOPPPSSSS!!! I had read your text in mail in my inbox but Missed to see your link.....hehe

Report message to a moderator

Previous Topic:	SQL -Start Date & End Date Issue
Next Topic:	about plsql table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 10:33:15 CDT 2024