Home » SQL & PL/SQL » SQL & PL/SQL » How to select Top N th sal by using Join query ? (merged)
How to select Top N th sal by using Join query ? (merged) [message #331918] Sun, 06 July 2008 10:36 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hello
To select Top N th sal I am using sub query like follows

In Oracle
----------
SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK()
OVER (ORDER BY sal DESC) sal_dense_rank
FROM emp )
WHERE sal_dense_rank = 5;

In Sql Server-2000
------------------
SELECT min(sal)
FROM (SELECT TOP 5 sal FROM emp order by sal desc) as A


But in one interview they asked me How to get top N th sal by using Join Query? Because performance wise join query is better then sub query.If any body knows please tell me.

by
sbmk_design
Re: How to select Top N th sal by using Join query ? [message #331919 is a reply to message #331918] Sun, 06 July 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no subquery in what you posted just inline view. This means there is one table scan.

By the way, the 2 queries you gave are not equivalent.

Regards
Michel
Re: How to select Top N th sal by using Join query ? [message #331935 is a reply to message #331918] Sun, 06 July 2008 14:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Well I guess it will be something in the flavour of this and this is one of the standard interview question been asked before oracle introduced the analytic functions (back in oracle 7).
select * from emp a where &no = (select coun(*) from emp where b.col1 >= a.col1)

Quote:
Because performance wise join query is better then sub query.

This is a too generic statement to make.

Regards

Raj
Re: How to select Top N th sal by using Join query ? [message #332036 is a reply to message #331919] Mon, 07 July 2008 05:27 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Please verify the following query.if it is not correct please correct me

Select A.Ename,A.sal,A.job 
  From EMP A
 Where &N=( Select Count(Distinct(B.sal)) 
              From EMP B
             Where A.sal<=B.sal);



Thank you.

Re: How to select Top N th sal by using Join query ? [message #332040 is a reply to message #332036] Mon, 07 July 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is wrong is that it is Oracle 7 way to do it; it is no more the way since beginning of this century.

Regards
Michel
Re: How to select Top N th sal by using Join query ? [message #332045 is a reply to message #332040] Mon, 07 July 2008 05:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
How many times this question is answered in this forum.
I think in a week this kind of question comes.

No body try to use google and just post it here.

Regards,
Rajat
Re: How to select Top N th sal by using Join query ? [message #332048 is a reply to message #331918] Mon, 07 July 2008 06:00 Go to previous messageGo to next message
gurupatham
Messages: 66
Registered: March 2008
Location: Chennai
Member
To Select Top Nth Salary Using Join.
select max(e1.sal) 
from emp e1 , (select distinct sal from emp) e2 
where e1.sal <= e2.sal 
group by e1.empno  
having count(1) = &n

[Updated on: Mon, 07 July 2008 06:45] by Moderator

Report message to a moderator

Re: How to select Top N th sal by using Join query ? [message #332055 is a reply to message #332048] Mon, 07 July 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your query, don't put it in a single line.

This is still an out of date way to do.

Regards
Michel
please explain how this query is fetching the rows? [message #332081 is a reply to message #331918] Mon, 07 July 2008 08:12 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
here is a query to find the top 3 salaries. But the thing is that i am now able to understand how its working to get the correct data :How the data in the alias table P1 and P2 getting compared. Can you please explain in some steps.

SELECT MIN(P1.SAL) FROM PSAL P1, PSAL P2
2 WHERE P1.SAL >= P2.SAL
3 GROUP BY P2.SAL 
4 HAVING COUNT (DISTINCT P1.SAL) <=3 ;


here is the data i used :

SQL> select * from psal;

NAME SAL
---------- ----------
able 1000
baker 900
charles 900
delta 800
eddy 700
fred 700
george 700
george 700


And the query result is as follows:

MIN(P1.SAL)
-----------
800
900
1000


I want to see the top 3 distinct salaries.
Re: please explain how this query is fetching the rows? [message #332083 is a reply to message #332081] Mon, 07 July 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the having clause, put the count inside the select list and study the result.

Regards
Michel
Re: How to select Top N th sal by using Join query ? [message #333887 is a reply to message #332055] Mon, 14 July 2008 14:10 Go to previous message
Bonita
Messages: 32
Registered: June 2008
Member
renu4dwh,

Do you understand its steps ? I'm still confused .

Can you share your thoughts in step by step ? Thanks.
Previous Topic: Delete statement on Huge table is very slow
Next Topic: ^ character
Goto Forum:
  


Current Time: Sun Dec 11 04:39:36 CST 2016

Total time taken to generate the page: 0.05508 seconds