Home » SQL & PL/SQL » SQL & PL/SQL » Top - N queries in oracle
Top - N queries in oracle [message #272754] Sun, 07 October 2007 01:12 Go to next message
sudarshan89
Messages: 1
Registered: October 2007
Junior Member
Hi this a query to find the employees with the lowest salaries (3 lowest) it works fine !!

select salary,rownum from (select salary,rownum from employees order by salary) where rownum <= 3

But when i try to find the 3rd lowest salary


select salary,rownum from (select salary,rownum from employees order by salary) where rownum = 3

it shows no data found

The queries have been run on the HR database .. get free wid oracle !!

Tell me where i am going wrong !

[mod-edit] removed illiterate IM words.

[Updated on: Mon, 08 October 2007 07:54] by Moderator

Report message to a moderator

Re: Top - N queries in oracle [message #272755 is a reply to message #272754] Sun, 07 October 2007 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ROWNUM Pseudocolumn

Use your current query as a subquery and select only the third row in outer query.

Quote:

u get free wid oracle

Please read and follow OraFAQ Forum Guide, especially sections on IM speak and formatting.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Top - N queries in oracle [message #272756 is a reply to message #272754] Sun, 07 October 2007 01:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How did you get the idea that your query works??
SQL> select sal from emp order by sal;

       SAL
----------
       800
       950
      1100
      1250
      1250
      1300
      1500
      1600
      2450
      2850
      2975
      3000
      3000
      5000

SQL> select *
  2  from   (select sal
  3          ,      rownum rn
  4          from   emp
  5          order  by sal
  6        )
  7  where  rn = 3
  8  /

       SAL         RN
---------- ----------
      1250          3
If you omit the outer where clause, you can see why:
SQL> select *
  2  from   (select sal
  3          ,      rownum rn
  4          from   emp
  5          order  by sal
  6        )
  7  /

       SAL         RN
---------- ----------
       800          1
       950         12
      1100         11
      1250          3
      1250          5
      1300         14
      1500         10
      1600          2
      2450          7
      2850          6
      2975          4
      3000          8
      3000         13
      5000          9

You cannot use rownum like that, you have to use row_number() instead of rownum. (or add a nesting level)
SQL> select *
  2  from   (select sal
  3          ,      rownum rn
  4          from   (select sal
  5                  from   emp
  6                  order  by sal
  7                 )
  8        )
  9  /

       SAL         RN
---------- ----------
       800          1
       950          2
      1100          3
      1250          4
      1250          5
      1300          6
      1500          7
      1600          8
      2450          9
      2850         10
      2975         11
      3000         12
      3000         13
      5000         14

SQL> select *
  2  from   (select sal
  3          ,      row_number() over (order by sal) rn
  4          from   emp
  5          order  by sal
  6        )
  7  /

       SAL         RN
---------- ----------
       800          1
       950          2
      1100          3
      1250          4
      1250          5
      1300          6
      1500          7
      1600          8
      2450          9
      2850         10
      2975         11
      3000         12
      3000         13
      5000         14

And more: you have to alias that column in the inner query and use that alias in the outer where (that was also an error in your second query)

SQL> select *
  2  from   (select sal
  3          ,      row_number() over (order by sal) rn
  4          from   emp
  5          order  by sal
  6        )
  7  where  rn = 3
  8  /

       SAL         RN
---------- ----------
      1100          3


[Edit: This is what Michel meant; he posted while I was creating this reply]

[Updated on: Sun, 07 October 2007 01:29]

Report message to a moderator

Re: Top - N queries in oracle [message #272759 is a reply to message #272756] Sun, 07 October 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not what I meant. Actually I didn't notice the rownum in the inner query.
This inner rownum is ignored by Oracle in the outer query.
The rownum in the outer query is the rownum of the outer query.

SQL> select sal,rownum
  2  from emp 
  3  order by sal
  4  /
       SAL     ROWNUM
---------- ----------
       800          1
       950         12
      1100         11
      1250          3
      1250          5
      1300         14
      1500         10
      1600          2
      2450          7
      2850          6
      2975          4
      3000          8
      3000         13
      5000          9

14 rows selected.

SQL> select sal, rownum 
  2  from ( select sal,rownum 
  3         from emp 
  4         order by sal )
  5  /
       SAL     ROWNUM
---------- ----------
       800          1
       950          2
      1100          3
      1250          4
      1250          5
      1300          6
      1500          7
      1600          8
      2450          9
      2850         10
      2975         11
      3000         12
      3000         13
      5000         14

14 rows selected.

In fact, what I was trying to say is:
SQL> select sal, rn
  2  from ( select sal, rownum rn
  3         from ( select sal /*,rownum */ 
  4                from emp 
  5                order by sal ) 
  6         where rownum <= 3 )
  7  where rn = 3
  8  /
       SAL         RN
---------- ----------
      1100          3

1 row selected.


Regards
Michel

Re: Top - N queries in oracle [message #272853 is a reply to message #272759] Mon, 08 October 2007 01:14 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Rownum problem (old thread)
If you search the board you'll find similar threads.

MHE

[Updated on: Mon, 08 October 2007 01:14]

Report message to a moderator

Previous Topic: Service name
Next Topic: Converting Blob to Clob
Goto Forum:
  


Current Time: Sun Dec 04 06:27:18 CST 2016

Total time taken to generate the page: 0.11101 seconds