Top n query tie problem...

S

stephen.h.dow

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.
 
G

Guest

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
 
J

John Spencer

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
 
S

stephen.h.dow

John and Lucas - Thanks!

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