Re: Ranking multiple Columns
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Fri, 01 Sep 2006 11:10:26 -0500
Bob wrote:
I am trying to Rank multiple columns in a query. I can Rank [Tpoints] field
with no problem. What I am trying to do is that if the first field is equal
then use the [YTD Msn] field to detrmine the rank number and so on if the
second field is equal.
Like rank 8 since Tpoints is 216 then use YTD Msn to determine who is 8 or
9. In this case the one with 112.50% is Rank 8 and the one with 104.17% is
Rank 9. Then rest resume use Tpoints until it equals again.
Rank TPoints YTD Msn ShipRate NPSSplit
1 289 213.64% 100.00% 14.89%
2 282 141.67% 87.10% 79.41%
3 262 193.75% 100.00% 93.55%
4 253 137.50% 94.74% 63.64%
5 243 112.50% 100.00% 66.67%
6 237 140.91% 86.96% 54.84%
7 232 130.43% 100.00% 26.67%
8 216 104.17% 100.00% 68.00%
8 216 112.50% 100.00% 51.85%
10 213 126.32% 100.00% 79.17%
11 206 125.00% 94.74% 60.00%
12 203 127.27% 85.71% 82.14%
13 200 115.00% 100.00% 60.87%
13 200 120.83% 89.47% 58.62%
15 192 112.50% 100.00% 83.33%
SELECT (SELECT COUNT(*)
FROM tblGreen7Summary AS T1
WHERE T1.[TPoints]>T.[TPoints])+1 AS Rank, T.[TPoints], T.[YTD Msn],
T.[ShipRate], T.NPSSplit
FROM tblGreen7Summary AS T
ORDER BY T.[TPoints] DESC , T.[YTD Msn], T.[ShipRate], T.NPSSplit;
Try something like:
SELECT (SELECT COUNT(*)
FROM tblGreen7Summary AS T1
WHERE T1.[TPoints]>T.[TPoints]
OR ((T1.[TPoints]=T.[TPoints]
And T1. [YTD Msn]>T. [YTD Msn])
OR (T1.[TPoints]=T.[TPoints]
And T1. [YTD Msn]=T. [YTD Msn]
And T1.ShipRate <T.ShipRate))
)+1 AS Rank,
T.[TPoints], T.[YTD Msn], T.[ShipRate], T.NPSSplit
FROM tblGreen7Summary AS T
ORDER BY T.[TPoints] DESC , T.[YTD Msn],
T.[ShipRate], T.NPSSplit;
--
Marsh
MVP [MS Access]
.
- Prev by Date: Re: Help with using form text box to change a query criteria
- Next by Date: Re: Help with using form text box to change a query criteria
- Previous by thread: Query links
- Next by thread: Field too small? appending to yes/no field
- Index(es):
Relevant Pages
|
