How to count groups of rows 1

[spongie1]

I have this:

mdate mshift mmachine mQty
01/01/2004 1 machineA 50
01/01/2004 1 machineA 25
01/01/2004 1 machineA 15
01/01/2004 1 machineB 65
01/01/2004 1 machineB 45
01/01/2004 1 machineB 15
01/01/2004 2 machineA 55
01/01/2004 2 machineA 45
01/01/2004 2 machineA 10


I want this:

mdate mshift mmachine mQty RANK
01/01/2004 1 machineA 50 1
01/01/2004 1 machineA 25 2
01/01/2004 1 machineA 15 3
01/01/2004 1 machineB 65 1
01/01/2004 1 machineB 45 2
01/01/2004 1 machineB 15 3
01/01/2004 2 machineA 55 1
01/01/2004 2 machineA 45 2
01/01/2004 2 machineA 10 3

I have a list of the top 3 quantities for each date, shift, and machine group. I want to rank these in order from greatest to least.

 

[PHV]

Something like this ?
SELECT mdate, mshift, mmachine, mQty,
(SELECT Count(*) FROM tblSpongle WHERE mdate=A.mdate AND mshift=A.mshift AND mmachine=A.mmachine AND mQty>=A.mQty) As Rank
FROM tblSpongle A
;

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

 

[spongie1]

You are good. I have one case where I have a 3-way tie for 2nd and it writes 4 for each rank, but I did not ask for a method for handling ties... you gave me exactly what I asked for.

Your help is greatly appreciated. You definitely deserve a star.