Re: SQL Puzzle: Selecting top highest paid workers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 22 Oct 1999 16:11:52 -0400
Message-ID: <HsMQODSNwEnnhRVtfnL5T+a3AY6B@4ax.com>

A copy of this was sent to andreyNSPAM_at_bookexchange.net (NetComrade) (if that email address didn't require changing) On Fri, 22 Oct 1999 18:45:09 GMT, you wrote:

>Hi,
>
>I think I misrepresented my previous post.
>I need to know the top highest paid workers.
>Ot top 50 highest anything.. :) for a nonsorted table of course...
>

it is a trick question.

what if there are 51 people -- all making the same exact amount.

one possible answer is you want some "random" sample of these 51 people -- you want 50 of them.

another is you want the null set.

what if there are 25 people making X and 50 people making Y (X>Y and there are no other records). Do you want 25 records or 50? if 50 -- which 50?

scott_at_8i> select ename, sal from emp order by sal 2 /

ENAME SAL
---------- ----------

SMITH             800
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000
FORD             3000
KING             5000

14 rows selected.

This query gets you the a set that is less then or equal to what you want. For example, if i ask for the 2 highest paid people -- i get one record (since scott and ford are tied for second).

When i ask for the top 5 -- i get 5 since that is not ambigous.

scott_at_8i> select ename, sal

  2          from emp a
  3         where 2 > ( select count(*) from emp b where b.sal >= a.sal and

a.rowid <> b.rowid )
4 /

ENAME SAL
---------- ----------
KING 5000 scott_at_8i> select ename, sal

  2          from emp a
  3         where 5 > ( select count(*) from emp b where b.sal >= a.sal and

a.rowid <> b.rowid )
4 /

ENAME SAL
---------- ----------

JONES            2975
BLAKE            2850
SCOTT            3000
KING             5000
FORD             3000

this next query -- which ONLY WORKS IN Oracle8i, release 8.1 and up -- gets the first 2 or 5....

scott_at_8i>
scott_at_8i> select *
2 from ( select ename, sal from emp order by -sal ) 3 where rownum <= 2
4 /

ENAME SAL
---------- ----------

KING             5000
SCOTT            3000

scott_at_8i> select *
2 from ( select ename, sal from emp order by -sal ) 3 where rownum <= 5
4 /

ENAME SAL
---------- ----------

KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

>Thanx
>---------------
>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
>AOL: NetComrade ICQ: 11340726 remove NSPAM to email

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 22 1999 - 15:11:52 CDT