Home » Other » Training & Certification » Please help me
Please help me [message #277011] Sat, 27 October 2007 23:40 Go to next message
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.
Re: Please help me [message #277017 is a reply to message #277011] Sun, 28 October 2007 00:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
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 Go to previous messageGo to next message
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

Re: Please help me [message #277038 is a reply to message #277011] Sun, 28 October 2007 03:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: Please help me [message #277065 is a reply to message #277045] Sun, 28 October 2007 06:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you mean you gave an incorrect answer on purpose?
Now, what good would that do?
Re: Please help me [message #277079 is a reply to message #277078] Sun, 28 October 2007 10:03 Go to previous messageGo to next message
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
Cool
Re: Please help me [message #277090 is a reply to message #277079] Sun, 28 October 2007 15:27 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you verify Michel is 51?
Re: Please help me [message #277118 is a reply to message #277090] Mon, 29 October 2007 00:20 Go to previous messageGo to next message
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 Go to previous message
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:
  


Current Time: Tue Dec 06 04:53:19 CST 2016

Total time taken to generate the page: 0.11483 seconds