Ranking multiple Columns

[Bob]

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.

Any help would be greatly appreciated.

Thanks Bob

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;

 

[Marshall Barton]

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;

 

[Bob]

You are my hero. Thanks

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;