SQL Puzzle: Selecting top highest paid workers
NetComrade
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...
Thanx
---------------
Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
AOL: NetComrade ICQ: 11340726 remove NSPAM to email
Thomas Kyte
(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@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@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@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@8i>
scott@8i> select *
2 from ( select ename, sal from emp order by -sal )
3 where rownum <= 2
4 /
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
scott@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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Nick Willemse
Oracle has got an intenal identifier counting the rows in a sql statement
call ROWNUM
You may try to use it as follows:
SELECT Salary, Name
FROM Employees
WHERE ROWNUM < 51
ORDER BY Salary DESC
and it should give you the top 50 salary earners...
good luck
Nick
NetComrade wrote in message <3810b063....@news.earthlink.net>...
>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...
>
NetComrade
Van Messner
I posted this solution the other night. One guy said it didn't work,
everyone else ignored it. This is the solution that allows for ties. I
assumed that salaries might often be the same for more than one individual
and that what you wanted was to know the fifty most highly compensated
people in your company. It is a general solution for ranking with a
condition. The subquery assigns a rank to each salary, the outer query
gives you the answer you want. I chose to make a particular year the
condition.
I got to Oracle SQL and tried this solution on an 8.1 database. It does
work. The awkward lack of a column alias in the outer where clause is
because Oracle has a problem accepting the inner alias. Try this solution
to convince yourself.
Van
1 create table bigbucks (
2 name VARCHAR2(20),
3 salary NUMBER(8,2),
4* year NUMBER(4))
Table created.
SQL> insert into bigbucks values ('JOE', 5000, 1999);
SQL> insert into bigbucks values ('JACK', 4000, 1999);
SQL> insert into bigbucks values ('JOHN', 2500, 1999);
SQL> insert into bigbucks values ('JERRY',3000, 1999);
SQL> insert into bigbucks values ('JUNE',4000,1999);
SQL> insert into bigbucks values ('JULIE',1500,1998);
SQL> insert into bigbucks values ('JUAN',2500,1998);
SQL> insert into bigbucks values ('JIM',1000,1999);
SELECT
sal1.year, sal1.salary,
(SELECT
COUNT(DISTINCT sal2.salary)
FROM
bigbucks sal2
WHERE
sal2.salary >= sal1.salary
and 1999 = sal1.year) rnk
FROM
bigbucks sal1
WHERE (SELECT
COUNT(DISTINCT sal2.salary)
FROM
bigbucks sal2
WHERE
sal2.salary >= sal1.salary
and 1999 = sal1.year) > 0
ORDER BY
rnk;
YEAR SALARY RNK
--------- --------- ---------
1999 5000 1
1999 4000 2
1999 4000 2
1999 3000 3
1999 2500 4
1999 1000 6
6 rows selected.
NetComrade <andre...@bookexchange.net> wrote in message
news:3810b063....@news.earthlink.net...
Jurij Modic
<vmes...@netaxis.com> wrote:
>Hello Andrey:
>
> I posted this solution the other night. One guy said it didn't work,
>everyone else ignored it. This is the solution that allows for ties. I
>assumed that salaries might often be the same for more than one individual
>and that what you wanted was to know the fifty most highly compensated
>people in your company. It is a general solution for ranking with a
>condition. The subquery assigns a rank to each salary, the outer query
>gives you the answer you want. I chose to make a particular year the
>condition.
> I got to Oracle SQL and tried this solution on an 8.1 database. It does
>work. The awkward lack of a column alias in the outer where clause is
>because Oracle has a problem accepting the inner alias. Try this solution
>to convince yourself.
>
>Van
Hello Van
Your solution (although I'm not sure it returns the result the
original poster was looking for) works only in 8i, not in 8.0 or
lower. Here is the query that returns the same result in 7.2 or
higher.
SELECT /*+ rule */
year, salary, v1.rnk
FROM
bigbucks,
(SELECT
sal, rownum rnk
FROM
(SELECT
DISTINCT -salary sal
FROM
bigbucks
WHERE year=1999)) v1
WHERE
salary = -v1.sal
AND year = 1999
ORDER BY
v1.rnk;
YEAR SALARY RNK
---------- ---------- ----------
1999 5000 1
1999 4000 2
1999 4000 2
1999 3000 3
1999 2500 4
1999 1000 5
6 rows selected.
Regards,
Jurij Modic <jmo...@src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer
Van Messner
has Oracle changed in version 8i in terms of basic SQL queries? I can
understand different optimizations and so on, but I hadn't heard that they'd
altered what was acceptable in plain old SQL.
Van
Jurij Modic <jmo...@src.si> wrote in message
news:38123c8e...@news.siol.net...
Thomas Kyte
(if that email address didn't require changing)
> I don't have a version 7 or 8 here to play with but I'm surprised. What
>has Oracle changed in version 8i in terms of basic SQL queries? I can
>understand different optimizations and so on, but I hadn't heard that they'd
>altered what was acceptable in plain old SQL.
>
we are always adding features to SQL with each release. In Oracle8i, release
8.1, one of the new features is the ability to order an inline view (or any
subquery or view for that matter). makes "top n" queries relatively easy:
select *
from ( select * from emp order by sal desc )
where rownum <= 5;
will return:
1 select ename, sal
2 from ( select * from emp order by sal desc )
3* where rownum <= 5
scott@8i> /
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
In prior releases this would give you:
scott@8.0> l
1 select ename, sal
2 from ( select * from emp order by sal desc )
3* where rownum <= 5
scott@8.0> /
from ( select * from emp order by sal desc )
*
ERROR at line 2:
ORA-00907: missing right parenthesis
fumi
Van Messner <vmes...@netaxis.com> wrote in message
news:bkoQ3.32797$E_1.1...@typ11.nn.bcandid.com...
> Hello Andrey:
>
> I posted this solution the other night. One guy said it didn't work,
> everyone else ignored it. This is the solution that allows for ties. I
> assumed that salaries might often be the same for more than one individual
> and that what you wanted was to know the fifty most highly compensated
> people in your company. It is a general solution for ranking with a
> condition. The subquery assigns a rank to each salary, the outer query
> gives you the answer you want. I chose to make a particular year the
> condition.
> I got to Oracle SQL and tried this solution on an 8.1 database. It does
> work. The awkward lack of a column alias in the outer where clause is
> because Oracle has a problem accepting the inner alias. Try this solution
> to convince yourself.
>
> Van
>
> 1999 4000 2
> 1999 4000 2
> 1999 3000 3
> 1999 2500 4
>
> 6 rows selected.
Why did you use such a complicate condition?
You just listed the record with year=1999.
Jurij Modic
<vmes...@netaxis.com> wrote:
> I don't have a version 7 or 8 here to play with but I'm surprised. What
>has Oracle changed in version 8i in terms of basic SQL queries? I can
>understand different optimizations and so on, but I hadn't heard that they'd
>altered what was acceptable in plain old SQL.
>
>Van
Thomas pointed out one of the new SQL features in 8i (ORDER BY inside
a view). However you did not use this kind of construct in your query,
so this is not where you query would fail in pre-8i releases.
You used another new 8i SQL feature that was not allowed in earlier
release: the use of a select clause in a column list:
SELECT col1, col2,
(SELECT colx FROM ... WHERE ...) -- this is only allowed in 8i
FROM ...
HTH,
Thomas Kyte
(if that email address didn't require changing)
>On Sun, 24 Oct 1999 10:37:21 -0400, "Van Messner"
><vmes...@netaxis.com> wrote:
>
>> I don't have a version 7 or 8 here to play with but I'm surprised. What
>>has Oracle changed in version 8i in terms of basic SQL queries? I can
>>understand different optimizations and so on, but I hadn't heard that they'd
>>altered what was acceptable in plain old SQL.
>>
>>Van
>
>Thomas pointed out one of the new SQL features in 8i (ORDER BY inside
>a view). However you did not use this kind of construct in your query,
>so this is not where you query would fail in pre-8i releases.
>
sorry -- the query they referred to wasn't in the post, i figured by the
subject, it must have been that (order by in a subquery)... however...
>You used another new 8i SQL feature that was not allowed in earlier
>release: the use of a select clause in a column list:
>
>SELECT col1, col2,
> (SELECT colx FROM ... WHERE ...) -- this is only allowed in 8i
>FROM ...
>
Now, that one is a new one on me :)
It actually works. Its not documented AFAIK but it works. I'll bug the doc to
get it documented.
>HTH,
>Jurij Modic <jmo...@src.si>
>Certified Oracle DBA (7.3 & 8.0 OCP)
>================================================
>The above opinions are mine and do not represent
>any official standpoints of my employer