Top n query tie problem...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stephen.h.dow@gmail.com - 20 Jul 2006 16:42 GMT

I'm trying get the top 3 salaries (without ties) for each policy.
Here's my table and sql:

ID    Policy    Salary
1    100    100000
2    100    99999
3    100    99998
4    100    99997
5    100    99996
6    100    99995
7    100    99994
8    100    99993
9    100    99992
10    100    99991
11    100    99990
12    200    500000
13    200    500000
14    200    200000
15    200    200000
16    200    200000
17    200    200000
18    200    100000
19    200    100000
20    200    100000
21    300    100000
22    300    100000
23    300    99999
24    300    99999
25    300    99999
26    300    99999

sql: select one.policy, one.salary
from test as one
where (one.salary in (select top 3 two.salary
from test as two
where one.policy = two.policy
order by two.salary desc, two.ID asc))

The result set displays ties - so for policy 200, I get the 2 500k's
and 4 200k's.

Any help would be greatly appreciated.

Reply to this Message

Lucas Kartawidjaja - 20 Jul 2006 17:02 GMT

Try:

select distinct one.policy, one.salary
from test as one
where (one.salary in (select top 3 two.salary
from test as two
where one.policy = two.policy
order by two.salary desc, two.ID asc))

Hopefully that works.

Lucas

> I'm trying get the top 3 salaries (without ties) for each policy.
> Here's my table and sql:
[quoted text clipped - 38 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

John Spencer - 20 Jul 2006 17:19 GMT

I was going to suggest using DISTINCT in the outer query, but that may not
work to give you what you want.

This returns the salaries that are in the top three. So if , this should
return one 500 K and one 200K
SELECT Distinct one.policy, one.salary
FROM test as one
WHERE (one.salary in
(SELECT TOP 3 two.salary
FROM test as two
WHERE one.policy = two.policy
ORDER BY two.salary desc, two.ID asc))
ORDER By Policy, Salary

This returns the ID's that are in the top 3 by salary and then ID. This
should return 500K, 500K, 200K for policy 200
SELECT one.policy, one.salary
FROM test as one
WHERE ( One.ID in
(SELECT TOP 3 Two.ID
FROM test as two
WHERE one.policy = two.policy
ORDER BY two.salary desc, two.ID asc))
ORDER By Policy, Salary

> I'm trying get the top 3 salaries (without ties) for each policy.
> Here's my table and sql:
[quoted text clipped - 38 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

stephen.h.dow@gmail.com - 20 Jul 2006 17:31 GMT

John and Lucas - Thanks!

John - your second query is exactly what I was looking for. Thanks
again.

> I was going to suggest using DISTINCT in the outer query, but that may not
> work to give you what you want.
[quoted text clipped - 63 lines]
> >
> > Any help would be greatly appreciated.

Reply to this Message