OraFAQ Forum: Training & Certification

Home » Other » Training & Certification » Please help me

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

Please help me [message #277011]

Sat, 27 October 2007 23:40

jigart4586
Messages: 3
Registered: October 2007
Location: india

Junior Member

Please help me on solve this query :
display all information from emp whose salary is third maximum.

Thanx in advance.

Report message to a moderator

Re: Please help me [message #277017 is a reply to message #277011]

Sun, 28 October 2007 00:07

el33t
Messages: 29
Registered: October 2007

Junior Member

sql has many ways

HINT:

select min(salary) from (select salary from emp order by salary desc) where rownum < 4;

[Updated on: Sun, 28 October 2007 00:09]

Report message to a moderator

Re: Please help me [message #277023 is a reply to message #277011]

Sun, 28 October 2007 01:23

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

Senior Member
Account Moderator

I help you:
1/ try to do your homework and tell us where you're stuck
2/ search on board, this homework has been answered many times
3/ don't trust any answer, verify them

Regards
Michel

[Updated on: Sun, 28 October 2007 01:23]

Report message to a moderator

Re: Please help me [message #277035 is a reply to message #277011]

Sun, 28 October 2007 02:51

el33t
Messages: 29
Registered: October 2007

Junior Member

see "rownum" is not available in other db like MySQL only specific to oracle.

here is another way but too many subqueries is not good

select max(salary) from emp where salary <(select max(salary) from emp where salary <(select max(salary) from emp))

and yet another way:

select top 3 salary from emp where salary not in ( select top 2 salary from emp order by salary desc ) order by salary desc

Report message to a moderator

Re: Please help me [message #277038 is a reply to message #277011]

Sun, 28 October 2007 03:14

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

SQL> select top 3 salary from emp where salary not in ( 
select top 2 salary from emp order by salary desc ) order by 
salary desc
   ;
select top 3 salary from emp where salary not in ( select top 2 
salary from emp order by salary desc ) order by salary desc

           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

[Updated on: Sun, 28 October 2007 03:14]

Report message to a moderator

Re: Please help me [message #277045 is a reply to message #277038]

Sun, 28 October 2007 03:42

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I.e. there's no TOP in Oracle. I guess it would work on MySQL, but this is OraFAQ forum.

Report message to a moderator

Re: Please help me [message #277065 is a reply to message #277045]

Sun, 28 October 2007 06:05

el33t
Messages: 29
Registered: October 2007

Junior Member

I know that and i also said rownum is oracle specific...so i purposely gave top query..and arju and yourself , the oracle masters on this board,verified that. Cool

As Monsieur Michel say:

Quote:

3/ don't trust any answer, verify them

Thank You.

[Updated on: Sun, 28 October 2007 06:21]

Report message to a moderator

Re: Please help me [message #277078 is a reply to message #277065]

Sun, 28 October 2007 10:01

Frank
Messages: 7901
Registered: March 2000

Senior Member

Do you mean you gave an incorrect answer on purpose?
Now, what good would that do?

Report message to a moderator

Re: Please help me [message #277079 is a reply to message #277078]

Sun, 28 October 2007 10:03

el33t
Messages: 29
Registered: October 2007

Junior Member

just to support Sir Michel (respect for the 51 year old man Wink

)

Quote:

3/ don't trust any answer, verify them

Report message to a moderator

Re: Please help me [message #277090 is a reply to message #277079]

Sun, 28 October 2007 15:27

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you verify Michel is 51?

Report message to a moderator

Re: Please help me [message #277118 is a reply to message #277090]

Mon, 29 October 2007 00:20

el33t
Messages: 29
Registered: October 2007

Junior Member

well if he can put up his real picture then it can be really confirmed..whats his date of birth in his profile is indeed how old man he is Cool

(unless of course he has not done some plastic surgery or taking any "BOTOX" shots)

[Updated on: Mon, 29 October 2007 00:21]

Report message to a moderator

Re: Please help me [message #277119 is a reply to message #277011]

Mon, 29 October 2007 00:29

el33t
Messages: 29
Registered: October 2007

Junior Member

and I wish he was a database..i would have verified writing a complex query...but alas it would work on MichelDB and this is OraFAQ..

[Updated on: Mon, 29 October 2007 00:29]

Report message to a moderator

Previous Topic:	ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Next Topic:	institues giving training on Oracle CRM

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:38:23 CDT 2024