OraFAQ Forum: Training & Certification » Query regarding correlated sub-query and top-n

Home » Other » Training & Certification » Query regarding correlated sub-query and top-n

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

Query regarding correlated sub-query and top-n [message #289708]

Tue, 25 December 2007 14:30

rolex.mp
Messages: 161
Registered: February 2007

Senior Member

I am using Oracle Database 11g release 1 edition .

I want to get the top 3 earning employees of each department in the hr.employees table .
I used the below query to achieve the same

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- ------ ---------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

select hire_date,salary,employee_id,department_id from 
	(select hire_date,salary,
                rank() over (partition by department_id order by salary desc) top3,
                employee_id,department_id 
         from employees) 
	where top3<4;

Now can I get the same o/p using correlated sub-query and top N query ? I dont want to use any Oracle functions

[Updated on: Tue, 25 December 2007 14:53] by Moderator

Report message to a moderator

Re: Query regarding correlated sub-query and top-n [message #289710 is a reply to message #289708]

Tue, 25 December 2007 14:51

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So do it.
Why if you are in 11g uou don't want to use functions that are available in 11g?

Regards
Michel

[Updated on: Tue, 25 December 2007 14:53]

Report message to a moderator

Re: Query regarding correlated sub-query and top-n [message #289726 is a reply to message #289708]

Tue, 25 December 2007 22:24

rolex.mp
Messages: 161
Registered: February 2007

Senior Member

I was learning these functions and found them very useful . But I also had an idea that the same can be done using correlated sub-query combined with top-n .

Just thought of clarifying the same with you people . I tried with few ways but was not able to come to a solution using the same . Can anyone help me regarding this ?

Report message to a moderator

Re: Query regarding correlated sub-query and top-n [message #289731 is a reply to message #289726]

Tue, 25 December 2007 22:59

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Yes, you can achieve the same results by either:

1. Using a top-n method with rownum and order by. You would order the results in an inner subquery using the order by clase, then select the top rows filtering on rownum. This would probably be about the same speed as the analytic function, maybe slightly faster or slower, depending on the circumstances.

2. Using an even older method of using a correlated subquery. This method was used in versions that prohibited an order by clause in a subquery. You would compare the number of rows that you want to the count of rows in the correlated subquery that compares the values to the outer query. These is infamous for being extremely slow compared to newer methods.

If you want to test these and can't get the code right, then please post a copy and paste of what you have tried, including error message and/or results and indicate what you do not understand.

Report message to a moderator

Re: Query regarding correlated sub-query and top-n [message #289734 is a reply to message #289726]

Tue, 25 December 2007 23:19

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Here are a couple of similar examples using the emp table, selecting the top 5 rows based on hiredate. You could do the same for the top 3 rows based on descending salary.

-- top n:

SCOTT@orcl_11g> 
SCOTT@orcl_11g> SELECT *
  2  FROM   (SELECT a.*
  3  	     FROM   scott.emp a
  4  	     ORDER  BY a.hiredate)
  5  WHERE  ROWNUM <= 5
  6  ORDER BY hiredate
  7  /

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30

-- correlated subquery:

SCOTT@orcl_11g> SELECT *
  2  FROM   scott.emp b
  3  WHERE  5 >=
  4  	    (SELECT COUNT (*)
  5  	     FROM   scott.emp a
  6  	     WHERE  a.hiredate <= b.hiredate)
  7  ORDER BY hiredate
  8  /

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30

SCOTT@orcl_11g>

[Updated on: Tue, 25 December 2007 23:20]

Report message to a moderator

Previous Topic:	How to find the Space used by the objects and Max Space allocated for the objects
Next Topic:	SQL Tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 13:47:43 CST 2024