Re: SQL PUZZLE: selecting top 50 salaries in a table
From: Kevin Fries <kelfink_at_ecst.csuchico.edu>
Date: 22 Oct 1999 22:18:53 GMT
Message-ID: <7uqnsd$9o5$1_at_hubble.csuchico.edu>
;
Date: 22 Oct 1999 22:18:53 GMT
Message-ID: <7uqnsd$9o5$1_at_hubble.csuchico.edu>
In article <380f8e89.268436409_at_news.earthlink.net>,
NetComrade <andreyNSPAM_at_bookexchange.net> wrote:
>Well.. we know how to select top salary, which is select max(salary),
>but how would I select top 50 records?
>---------------
>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
>AOL: NetComrade ICQ: 11340726 remove NSPAM to email
>
I spotted a good article about this at oracle's oramag code and tip archive site:
http://www.oracle.com/oramag/code/tip05239.html
Here's how the article would solve this problem:
select name,salary
from emp aux1 where 3 >= ( select count(*) from emp aux2 where aux2.salary > aux1.salary or ( aux2.salary = aux1.salary and aux2.rowid >= aux1.rowid ) )
;
And it seems to work fine. It uses a correlated subquery,so it's not the most efficient thing in the world, but hey, it works.
Kevin
-- ------------------------------------------------------------- Kevin Fries kelfink_at_ecst.csuchico.edu CPD/PB, C Developer/DBA http://www.ecst.csuchico.edu/~kelfinkReceived on Sat Oct 23 1999 - 00:18:53 CEST