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@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/~kelfink Received on Fri Oct 22 1999 - 17:18:53 CDT