Home » SQL & PL/SQL » SQL & PL/SQL » frist three higher values
frist three higher values [message #18381] Wed, 30 January 2002 00:30 Go to next message
ams
Messages: 1
Registered: January 2002
Junior Member
can some one exlain how i can extract first three highest values from a same column in oracle, using a single query

thanx,

ams

Report message to a moderator

Re: frist three higher values [message #18383 is a reply to message #18381] Wed, 30 January 2002 01:17 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

here is an example

select *
from emp e1
where 3>(select count(*) from emp e2 where e2.sal>e1.sal)
/

cheers
pratap

Report message to a moderator

Re: frist three higher values [message #18385 is a reply to message #18381] Wed, 30 January 2002 02:10 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
The fastest/most performant way to get the top N result is to use the Oracle Ranking Functions.

SELECT sal
FROM
( SELECT sal,
ROW_NUMBER() OVER (ORDER BY sal) s_rank
FROM emp
)
WHERE s_rank<4;

Report message to a moderator

Re: frist three higher values [message #18396 is a reply to message #18381] Wed, 30 January 2002 05:05 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Note that the solutions provided by Mike & Pratap will give the same answer if the top three values are different, but not if there are duplicates. So the solution depends on if you want the highest three distinct values or simply the highest three values.

Report message to a moderator

Re: frist three higher values [message #18398 is a reply to message #18396] Wed, 30 January 2002 05:17 Go to previous messageGo to next message
Luc
Messages: 11
Registered: January 2002
Junior Member
What do you think of this one:

select value from
(select value from mytable
order by value desc)
where rownum <= 3

Report message to a moderator

Re: first three higher values [message #18400 is a reply to message #18396] Wed, 30 January 2002 05:37 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Should work but not if you want the three highest distinct values.

Report message to a moderator

Performance and Distinct Values [message #18416 is a reply to message #18396] Wed, 30 January 2002 08:07 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
You are right Jon, the result of my query and the one of Pratap will give the same answer. BUT there will be a different explain plan and with a larger amount of data there will be a big performance difference.

Depending how you define the ranking you can also use RANK() or DENSE_RANK()instead of ROW_NUMBER() . The difference between RANK and DENSE_RANK is that DENSE_RANK leaves no gaps in ranking
sequence when there are ties. That is, if you were ranking a competition using DENSE_RANK and
had three people tie for second place, you would say that all three were in second place and that
the next person came in third. The RANK function would also give three people in second place,
but the next person would be in fifth place.

Report message to a moderator

Previous Topic: Combine queries !!!
Next Topic: shared pool memory flush
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 12:10:16 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.