Home » SQL & PL/SQL » SQL & PL/SQL » Top 3 Sal.
Top 3 Sal. [message #378575] Wed, 31 December 2008 00:30 Go to next message
tipyyt
Messages: 11
Registered: March 2007
Location: Karachi
Junior Member

Data

SQL> select empno,sal from emp
2 order by sal desc
3 ;

EMPNO SAL
--------- ---------
7839 5000
7566 3000
7902 3000
7788 3000
7698 2850
7782 2450
7499 1600
7844 1500
7934 1300
7521 1250
7654 1250
7876 1100
7900 950
7369 800

14 rows selected.


Problem
I want Top 3 Salaries with empno of the Table

I wrote
select * from (select empno,sal from emp
order by sal desc)
WHERE ROWNUM <= 3


I got
EMPNO SAL
--------- ---------
7839 5000
7566 3000
7788 3000


Which is logically wrong Because 5000,3000 and 2850 are the top 3 salaries of the table

I want output that all employeess who are in the range of top 3 sal displayed.

Like That
EMPNO SAL
--------- ---------
7839 5000
7566 3000
7902 3000
7788 3000
7698 2850


Waiting for Reply
Regards
Danish Hayder





[Updated on: Wed, 31 December 2008 00:34]

Report message to a moderator

Re: Top 3 Sal. [message #378576 is a reply to message #378575] Wed, 31 December 2008 00:34 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Search the Forum and/or main site for "top n"; such a question is asked (and answered) on a regular basis.

P.S. Check the DENSE_RANK analytical function.

[Updated on: Wed, 31 December 2008 00:39]

Report message to a moderator

Re: Top 3 Sal. [message #378590 is a reply to message #378575] Wed, 31 December 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition,
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Top 3 Sal. [message #378724 is a reply to message #378575] Wed, 31 December 2008 23:11 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You can do like this also, for getting TOP 3 YEAR

SQL> select * from grp_test
  2  where year in
  3  (select * from
  4   (select distinct year from grp_test order by year desc)
  5   where rownum < 4);

YEAR     RESULT
---- ----------
2004       4000
2004       4000
2004       2200
2003        150
2003       2000
2002       1000


regards,
Delna
Re: Top 3 Sal. [message #378731 is a reply to message #378724] Thu, 01 January 2009 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.

Regards
Michel
Re: Top 3 Sal. [message #389770 is a reply to message #378575] Tue, 03 March 2009 09:14 Go to previous messageGo to next message
mejaime
Messages: 1
Registered: March 2009
Junior Member
You can resolve your problem by using Distinct function. I hope it can help.
There is an example below:

select sal 
from (select distinct( sal) from emp order by sal desc) 
where rownum < = 3
order by sal desc;

[Updated on: Tue, 03 March 2009 09:25]

Report message to a moderator

Re: Top 3 Sal. [message #389775 is a reply to message #378575] Tue, 03 March 2009 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't apply distinct to individual columns.
What you've put is logically equivalent to
select sal, empno 
from (select sal, empno from emp order by sal desc) 
where rownum < = 3
order by sal desc;


Which the OP has already tried and is wrong for his requirement.
Re: Top 3 Sal. [message #389778 is a reply to message #389775] Tue, 03 March 2009 09:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could get all analytical about the problem:
create table test_128 (col_1  number, col_2 number);

insert into test_128 values (1,3000);
insert into test_128 values (2,2900);
insert into test_128 values (3,2900);
insert into test_128 values (4,2800);
insert into test_128 values (5,2800);
insert into test_128 values (6,2800);
insert into test_128 values (7,2500);
insert into test_128 values (8,2300);

select col_1,col_2,dense_rank() over (order by col_2 desc) ranked_col_2
from   test_128;

COL_1	COL_2	RANKED_COL_2
1	3000	1
2	2900	2
3	2900	2
4	2800	3
6	2800	3
5	2800	3
7	2500	4
8	2300	5
Previous Topic: Password encryption
Next Topic: questions on code in PL/SQL by Example book
Goto Forum:
  


Current Time: Sun Dec 04 00:15:13 CST 2016

Total time taken to generate the page: 0.24905 seconds