frist three higher values [message #18381] |
Wed, 30 January 2002 00:30 |
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
|
|
|
|
Re: frist three higher values [message #18385 is a reply to message #18381] |
Wed, 30 January 2002 02:10 |
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;
|
|
|
Re: frist three higher values [message #18396 is a reply to message #18381] |
Wed, 30 January 2002 05:05 |
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.
|
|
|
|
|
Performance and Distinct Values [message #18416 is a reply to message #18396] |
Wed, 30 January 2002 08:07 |
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.
|
|
|