Rank Query

[cdogstu99]

I have a table with policy number, company code, premium.
I'm trying to create a new table and for each policy number rank the associated premium. So for Policy number 1, there are 10 different companies. I want something like this:

Policy Company Premium Rank
1 0 10 1
1 1 11 2
1 2 12 3
1 3 13 4
1 4 14 5
1 5 15 6
1 6 16 7
1 7 17 8
1 8 18 9
1 9 19 10

I have a query but it's giving me a rank of 1 for each rank.

SELECT Policies.Company, Policies.PK, Policies.Premium, Count(*) AS Rank INTO tblRank
FROM Policies AS Policies INNER JOIN Policies AS Winner ON Policies.Premium >=Winner.Premium AND Policies.PK=Winner.PK AND Policies.Company=Winner.Company
Group BY Policies.Company, Policies.PK, Policies.Premium
ORDER BY 4, 2, 3;

Can someone please help..Thanks!

 

[PHV]

What happens if you get rid of this ?
AND Policies.Company=Winner.Company

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cdogstu99]

That did it...Thanks!!!!