Home » SQL & PL/SQL » SQL & PL/SQL » i have a query to solve
i have a query to solve [message #442362] Sun, 07 February 2010 07:28 Go to next message
deepakhota
Messages: 9
Registered: December 2009
Location: Bangalore
Junior Member
i have a table named emp in which EMPNO ENAME,JOB,MGR,HIREDATE ,SAL,COMM,DEPTNO columns are there....

My requirement is to written a query like that which return top 5 highest salary with no duplicate SAL along with all columns...not only particular SAL column....

Re: i have a query to solve [message #442363 is a reply to message #442362] Sun, 07 February 2010 07:37 Go to previous messageGo to next message
manishsahu_21
Messages: 25
Registered: February 2010
Location: new delhi
Junior Member

first u have to wright the select satatement in the sub query wih order by clause sal desc, and by through u have to pick the all and outer query with have to where clause order by rownum.
Re: i have a query to solve [message #442364 is a reply to message #442363] Sun, 07 February 2010 07:44 Go to previous messageGo to next message
deepakhota
Messages: 9
Registered: December 2009
Location: Bangalore
Junior Member
but where duplicate salary are there.....
like

5000
6000
4500
5000
3000
5000
3500
5000
2000
then when i query then it results of top 5 sal like

6000
5000
5000
5000
5000

but my requirement is like

6000
5000
4500
3500
2000

Re: i have a query to solve [message #442366 is a reply to message #442362] Sun, 07 February 2010 07:58 Go to previous messageGo to next message
manishsahu_21
Messages: 25
Registered: February 2010
Location: new delhi
Junior Member


Use this one


select * from (select empno,ename,sal from emp order by sal desc)
where rownum<=5;
Re: i have a query to solve [message #442368 is a reply to message #442366] Sun, 07 February 2010 08:04 Go to previous messageGo to next message
deepakhota
Messages: 9
Registered: December 2009
Location: Bangalore
Junior Member
it will also not working....
it will also through duplicate value like...

5000
3000
3000
2975
2850
Re: i have a query to solve [message #442370 is a reply to message #442362] Sun, 07 February 2010 08:13 Go to previous messageGo to next message
manishsahu_21
Messages: 25
Registered: February 2010
Location: new delhi
Junior Member

select * from (select distinct sal from emp order by sal desc)
where rownum<=5

chk this
Re: i have a query to solve [message #442371 is a reply to message #442370] Sun, 07 February 2010 08:16 Go to previous messageGo to next message
deepakhota
Messages: 9
Registered: December 2009
Location: Bangalore
Junior Member
but it was showing only the SAL column...
but my requirement is to show the other column also along with SAl column...
Re: i have a query to solve [message #442373 is a reply to message #442362] Sun, 07 February 2010 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TOP n query is one the most asked question.
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: i have a query to solve [message #442374 is a reply to message #442373] Sun, 07 February 2010 08:20 Go to previous messageGo to next message
deepakhota
Messages: 9
Registered: December 2009
Location: Bangalore
Junior Member
what is the exact query...
Re: i have a query to solve [message #442376 is a reply to message #442374] Sun, 07 February 2010 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you try?

Regards
Michel
Re: i have a query to solve [message #442377 is a reply to message #442374] Sun, 07 February 2010 08:32 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
deepakhota wrote on Sun, 07 February 2010 15:20
what is the exact query...

It depends on the exact resultset which is required (as the one you posted does not seem to be "along with all columns", you required in your first post).
Anyway, you may find some Top N queries on http://www.orafaq.com/faq/how_does_one_select_the_top_n_rows_from_a_table or http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html.
Re: i have a query to solve [message #442380 is a reply to message #442364] Sun, 07 February 2010 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DENSE_RANK is exactly the function you need.

Regards
Michel
Re: i have a query to solve [message #442382 is a reply to message #442380] Sun, 07 February 2010 09:15 Go to previous messageGo to next message
deepakhota
Messages: 9
Registered: December 2009
Location: Bangalore
Junior Member
the dense_rank function also show the dulplicate salary..
i want no duplicate in salary...
Re: i have a query to solve [message #442383 is a reply to message #442382] Sun, 07 February 2010 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
deepakhota wrote on Sun, 07 February 2010 16:15
the dense_rank function also show the dulplicate salary..
i want no duplicate in salary...

Show what you did.
Show what you want.
If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.


Regards
Michel
Re: i have a query to solve [message #442433 is a reply to message #442382] Mon, 08 February 2010 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
deepakhota wrote on Sun, 07 February 2010 16:15
the dense_rank function also show the dulplicate salary..
i want no duplicate in salary...

You say you don't want to see duplicate salaries, yet when you were offered a query showing distinct salaries you complained that all you got was the salary-column.
Now if John and Jack both have a salary of 5000, which of the two should be returned? (And why?)
Re: i have a query to solve [message #445631 is a reply to message #442362] Wed, 03 March 2010 01:09 Go to previous messageGo to next message
pbutera
Messages: 5
Registered: January 2010
Junior Member
Deepakhota,
you seem to be using the classic emp table shown below:
SQL> select *
  2  from emp
  3  order by sal desc;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

14 rows selected.

Your problem is to avoid either SCOTT or FORD who are coming as duplicate in the highest top five salaries.
I took a PL/SQL option instead of an SQL one. Basically, I reordered the distinct salaries in a descending order and put all that in a cursor.
I then took a second cursor containing the rest of information that will be displayed only ONCE depending on the SAL value.
I have had a similar but somehow different question that required the rank function but as you see it here I didn't need it. I stay open for any efficiency suggestion. Below my code and output.
 
 1  declare
 2    cursor highsal_cursor is select distinct sal
 3                              from emp
 4                              order by sal desc;
 5    cursor emp_on_sal_cursor (v_sal number) is select empno, ename, job, mgr,                           hiredate, comm, deptno
 6                                               from emp
 7                                               where sal=v_sal;
 8    emp_on_sal_record emp_on_sal_cursor%ROWTYPE;
 9    v_sal number;
10    i number;
11  Begin
12    DBMS_OUTPUT.put_line('      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO');
13    DBMS_OUTPUT.put_line(' ---------- ---------- --------- ---------- --------- ---------- ---------- -----
14    open highsal_cursor;
15      i:=1;
16      fetch highsal_cursor into v_sal;
17      while highsal_cursor%FOUND and (i<=5) LOOP
18        open emp_on_sal_cursor (v_sal);
19          fetch emp_on_sal_cursor into emp_on_sal_record;
20          DBMS_OUTPUT.put_line(lpad(emp_on_sal_record.empno,11)||' '||
21                               rpad(emp_on_sal_record.ename,10)||' '||
22                               rpad(emp_on_sal_record.job,9)||' '||
23                               lpad(nvl(to_char(emp_on_sal_record.mgr),' '),10)||' '||
24                               lpad(emp_on_sal_record.hiredate,9)||' '||
25                               lpad(v_sal,10)||' '||
26                               lpad(nvl(to_char(emp_on_sal_record.comm),' '),10)||' '||
27                               lpad(emp_on_sal_record.deptno,10));
28          close emp_on_sal_cursor;
29          fetch highsal_cursor into v_sal;
30          i:=i+1;
31        end loop;
32        close highsal_cursor;
33* end;
SQL> /
      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
       7839 KING       PRESIDENT            17-NOV-81       5000                    10
       7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

PL/SQL procedure successfully completed.

Note: In order to have a formatted output from the DBMS_OUTPUT package, use
set serveroutput on wrapped text
instead of the classic
set serveroutput on
You can see the difference in the execution of line 12.
Regards.
Re: i have a query to solve [message #445637 is a reply to message #445631] Wed, 03 March 2010 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could you suggest any solution when you don't the an,swer to the following question?

Quote:
Now if John and Jack both have a salary of 5000, which of the two should be returned? (And why?)


In addition, most likely there is no need of PL/SQL, just a single SQL statement (as your PL/SQL block could be written with a single SQL).

Regards
Michel
Re: i have a query to solve [message #445642 is a reply to message #445637] Wed, 03 March 2010 02:23 Go to previous messageGo to next message
pbutera
Messages: 5
Registered: January 2010
Junior Member
Michel,
the case of two employees with the same salary is considered here with SCOTT and FORD who both have a salary of $3000. What I understood is that Deepakhota wanted the five highest salaries with a corresponding employee next to it. Otherwise, you can consider the question has no answer because you can't ignore the case when two employees have the same salary.
You can any kind of additional criteria (order by ename, deptno or any other available) to choose between employees with the same salary. I choose to take the first coming from the second cursor and you could make a different choice. Consider it is a problem with a non unique solution.
And I made different unsuccessful tests with SQL and I'd be glad to see how you do that.
Regards,
Pierre.
Re: i have a query to solve [message #445714 is a reply to message #442362] Wed, 03 March 2010 09:34 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
SELECT sname,sal
FROM
  (SELECT sname,
     sal,
     MAX(rowid) over(PARTITION BY salrank
   ORDER BY sal DESC) maxsal
   FROM
    (SELECT rowid,
       a.*
     FROM
      (SELECT sname,
         sal,
         dense_rank() over(
       ORDER BY sal DESC) salrank
       FROM tsal)
    a
     WHERE salrank <= 3)
  )
WHERE rowid = maxsal


Solved by Ramesh
Re: i have a query to solve [message #445716 is a reply to message #445714] Wed, 03 March 2010 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Solved by Ramesh

What is solved? Surely not OP question as we still have not the deterministic requirements.

Regards
Michel
Re: i have a query to solve [message #445726 is a reply to message #445714] Wed, 03 March 2010 10:28 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
@Ramesh: What about simplifying it a little
SQL> select *
  2  from (
  3    select empno, ename, hiredate, sal,
  4      dense_rank() over (order by sal desc) dr,
  5      row_number() over (partition by sal order by hiredate) rn1,
  6      row_number() over (partition by sal order by empno) rn2,
  7      row_number() over (partition by sal order by ename) rn3
  8    from emp )
  9  where dr <= 3
 10  order by dr;

     EMPNO ENAME      HIREDATE         SAL         DR        RN1        RN2        RN3
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7839 KING       17-NOV-81       5000          1          1          1          1
      7902 FORD       03-DEC-81       3000          2          1          2          1
      7788 SCOTT      09-DEC-82       3000          2          2          1          2
      7566 JONES      02-APR-81       2975          3          1          1          1

4 rows selected.

SQL> 
and filter to rows with RN# (according to the requirements) = 1?

Anyway, as original poster did not answer to Frank's question, my guess is that he/she found the requirement self-contradictory and (somehow) changed it. So all these posts are just SQL exercises for fun. Anyway, why not.
Re: i have a query to solve [message #445973 is a reply to message #445726] Thu, 04 March 2010 23:51 Go to previous message
pbutera
Messages: 5
Registered: January 2010
Junior Member
well, it's true that all this remains a pure SQL exercise since Deepakhota has given up, probably for the reason mentioned by Flyboy.
I have tried Ramesh solution and got the following output:
     (SELECT rowid,
      *
ERROR at line 8:
ORA-01446:cannot select ROWID from view with DISTINCT, GROUP BY, etc.

I splitted the code and created a view to somehow make the group function (here the dense_rank function) "less visible" and it worked.
I ended up with an output similar to flyboy's one (only ename and sal columns).
I don't understand where dr<=3 in the where clause is coming from. Because Deepakhota wanted the top five distinct highest salaries. You could have put dr<=4 to get five rows in the but this means that you already know that you have two employees with the same salary. if you put dr<=5, you'll get a six rows output. In my understanding, one could only put five somewhere in the code to get five five rows out.
So we basically ain't there yet!
Question for Michel Cadot: what do you mean by "deterministic requirements"?
Thanks in advance for your answer.
Previous Topic: Need help on to get the number of records (not a simple count())
Next Topic: Sybase str
Goto Forum:
  


Current Time: Sun Sep 25 09:31:43 CDT 2016

Total time taken to generate the page: 0.06289 seconds