Rank a Rank?

  • Thread starter Thread starter accesshacker
  • Start date Start date
A

accesshacker

I have a subquery that ranks stores based on their type and then by their
Sales. Now I would like to place them in a straight rank. I can't use Sales
since there can be a store ranked as an A that has Sales that places it in
the B. Here is what I have now and what I want/need to get to.

SN ST_TYPE Rank (Want)
20 A 1 1
3 A 2 2
5 A 3 3
89 A 4 4
10 A 5 5
9 B 1 6
43 B 2 7
16 B 3 8
19 B 4 9
8 B 5 10
42 B 6 11
25 B 7 12
7 B 8 13
46 B 9 14
38 B 10 15
34 B 11 16
29 B 12 17
14 B 13 18

This is the SQL I am using for the 1st Ranking.
SELECT T.Deal, T.SN, T.ST_TYPE, T.[LY Sales], (SELECT COUNT(*)
FROM ALLOC_TYPE_STORE T1
WHERE T1.ST_TYPE = T.ST_TYPE and T1.[LY Sales] >= T.[LY Sales]) AS Rank
FROM ALLOC_TYPE_STORE AS T
ORDER BY T.ST_TYPE, T.[LY Sales] DESC;

Thanks in advance for assistance.
 
Worked perfectly, thanks greatly Ken!!

KenSheridan via AccessMonster.com said:
Try this:

SELECT Deal, SN, ST_TYPE, [LY Sales],
(SELECT COUNT(*)+1
FROM ALLOC_TYPE_STORE AS T2
WHERE T2.ST_TYPE <= T1.ST_TYPE
AND ( T2.[LY Sales] > T1.[LY Sales]
OR T2.ST_TYPE <> T1.ST_TYPE)) AS Rank
FROM ALLOC_TYPE_STORE AS T1
ORDER BY ST_TYPE, [LY Sales] DESC;

Note that by counting the rows returned by the subquery and adding one, where
the subquery is correlated with the outer query on the sales being greater
than rather than greater than or equal to the outer query's sales, this
handles ties within a store-type group correctly.

Ken Sheridan
Stafford, England
I have a subquery that ranks stores based on their type and then by their
Sales. Now I would like to place them in a straight rank. I can't use Sales
since there can be a store ranked as an A that has Sales that places it in
the B. Here is what I have now and what I want/need to get to.

SN ST_TYPE Rank (Want)
20 A 1 1
3 A 2 2
5 A 3 3
89 A 4 4
10 A 5 5
9 B 1 6
43 B 2 7
16 B 3 8
19 B 4 9
8 B 5 10
42 B 6 11
25 B 7 12
7 B 8 13
46 B 9 14
38 B 10 15
34 B 11 16
29 B 12 17
14 B 13 18

This is the SQL I am using for the 1st Ranking.
SELECT T.Deal, T.SN, T.ST_TYPE, T.[LY Sales], (SELECT COUNT(*)
FROM ALLOC_TYPE_STORE T1
WHERE T1.ST_TYPE = T.ST_TYPE and T1.[LY Sales] >= T.[LY Sales]) AS Rank
FROM ALLOC_TYPE_STORE AS T
ORDER BY T.ST_TYPE, T.[LY Sales] DESC;

Thanks in advance for assistance.
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top