Order values

[abdali]

Hi,

How can I retrieve specified number of highest value columns with a single query;

[kmesser]

Select the 5 highest empno's from scott.emp:

select * from scott.emp where rownum < 6 order by empno desc;

[jmodic]

Well, one has to be fair and explain that the kmesser's sollution is more an exception then a general sollution. If instead of EMPNO you put SAL column yo won't get the 5 employees with highest sallaries! The more general solution for top-N queries would be:

select * from (select * from emp order by sal desc) where rownum < 6;

This will return 5 top paid employees. It is also worth noting that the above query will work only in 8i, because you can't use ORDER BY inside a view prior to 8i. Kmesser's query will work also with earlier releases, but the results will be wrong with pre-8i databases, because the optimizer's operation STOPKEY, which causes the results to be correct, was introduced in 8i.

[kmesser]

jmodic:

Wow! Thanks for all that additional info. I simply opened a SQL*PLUS session, tested my initial idea, and it worked.

This just goes to show you how complex the most apppropriate solution can be, even if other solutions seem to work perfectly.

Thanks!

I still don't understand why it worked with empno, but does not work with salary?! . . . Can you explain that for me?



[Edited by kmesser on 05-06-2001 at 09:30 PM]

[dhani_in]

hi friend,

the query worked for empno because it is ur primary key column and has index on it. while rows are fetched by primary key column it will be fetched in ordered manner. so the query worked.
for sal column first rows are fetched as in the database and only first 5 rows are fetched using primary key and with the result set it will order so u are not able to get the right answer.
so order by works after rows are fetched from the database.

bye.

[Edited by dhani_in on 05-07-2001 at 12:19 AM]

[abdali]

Thanks,
But How I can do it in oracle 7 ?

[jmodic]

In Orcle7 the top-N and bottom-N queries are much more complicated (particularry the top-N, where you have to sort records in descending order) and much less efficient.

If we stick with the "top 5 paid employees" example from SCOTT.EMP, here are three different queries that will return correct results. The first one is the most "straightforward", but also by far the most inefficient for large tables. The last one is the fastest one, particulary if there is an index on the sorting column. Note also that if there are equal values at the end of the result set (e.g. if there are more then one employee with the same salary at the fifth position) the first query will return none of them, while other two will return only one of them.

1. With corelated subquery
SELECT * FROM scott.emp e1 WHERE 5 >=
(SELECT COUNT(sal)
FROM scott.emp e2
WHERE e2.sal >= e1.sal)
ORDER BY e1.sal DESC;

2. With inline view
SELECT emp.* FROM emp,
(SELECT rowid x, -1*sal FROM emp GROUP BY -1*sal, rowid) e2
WHERE emp.rowid = e2.x AND rownum <= 5
ORDER BY emp.sal DESC;

3. With "dummy" outer join
SELECT emp.* FROM emp, dual
WHERE -1*emp.sal = DECODE(dual.dummy(+), 'X', NULL, NULL)
AND rownum <= 10
ORDER BY emp.sal DESC;

HTH,

[abdali]

I need the employees who have the highest 3 values in the salary field
, even there more then one has the same value.
Ex.

Empno Sal
----- ---
1 1000
2 500
3 1000
4 800
5 700
6 250
7 800
8 100

The result has to be like :

Empno
-----
1
3
4
5
7

[rojy]

hi guys
how to get duplicate records from a table?

[pras]

Hi rojy

pls search the forumn somebody has already given the solution for the duplicate records...

pras