Ranking Question

[Bob]

I currently have a query that does ranking and it works. My problem or
questions is that the rank jumps from 1 to 6 and so on. I understand that
the first 5 are ties. Does anyone have an idea to make it 1 then 2? In
other words have it pick up after the rank number not the numbers of ties.
I appreciate any help with this as I am stumped.

SELECT (SELECT COUNT(*)
FROM [ShipStats2_Only] AS T1
WHERE T1.ShipPercentx > T.ShipPer )+1 AS ShipRank2, T.ShipPer
FROM [ShipStats2] AS T;

With Results like this:
ShipRank2 ShipPer
1 100.00%
1 100.00%
1 100.00%
1 100.00%
1 100.00%
6 95.65%
7 94.74%
8 93.75%
9 93.33%
9 93.33%
10 92.86%
10 92.86%
11 91.67%
11 91.67%
12 90.91%
12 90.91%

I want the results to look like this:
ShipRank2 ShipPer
1 100.00%
1 100.00%
1 100.00%
1 100.00%
1 100.00%
2 95.65%
3 94.74%
4 93.75%
5 93.33%
5 93.33%
6 92.86%
6 92.86%
7 91.67%
7 91.67%
8 90.91%
8 90.91%

 

[Gary Walter]

I currently have a query that does ranking and it works. My problem or
questions is that the rank jumps from 1 to 6 and so on. I understand that
the first 5 are ties. Does anyone have an idea to make it 1 then 2? In
other words have it pick up after the rank number not the numbers of ties.
I appreciate any help with this as I am stumped.

SELECT (SELECT COUNT(*)
FROM [ShipStats2_Only] AS T1
WHERE T1.ShipPercentx > T.ShipPer )+1 AS ShipRank2, T.ShipPer
FROM [ShipStats2] AS T;

With Results like this:
ShipRank2 ShipPer
1 100.00%
1 100.00%
1 100.00%
1 100.00%
1 100.00%
6 95.65%
7 94.74%
8 93.75%
9 93.33%
9 93.33%
10 92.86%
10 92.86%
11 91.67%
11 91.67%
12 90.91%
12 90.91%

I want the results to look like this:
ShipRank2 ShipPer
1 100.00%
1 100.00%
1 100.00%
1 100.00%
1 100.00%
2 95.65%
3 94.74%
4 93.75%
5 93.33%
5 93.33%
6 92.86%
6 92.86%
7 91.67%
7 91.67%
8 90.91%
8 90.91%

Hi Bob,

In a perfect world, the simple solution is to use
a DISTINCT variety of your "ShipStats2_Only"
query/table? in your correlated subquery...

qryDISTINCTShipStats2_Only

SELECT
DISTINCT
ShipPercentx
FROM
[ShipStats2_Only];

then...

SELECT
(SELECT COUNT(*)
FROM [qyrDISTINCTShipStats2_Only] AS T1
WHERE T1.ShipPercentx > T.ShipPer )+1 AS ShipRank2,
T.ShipPer
FROM [ShipStats2] AS T;

In a not-so-perfect world, if the "percent" fields
are floats, getting "distinct" may not be so easy.

But, if your "percent" values are Decimal or have
been rounded to strip everything after hundreths
place, above should work I would think.

good luck,

gary

 

[Guest]

Try these two queries ---
ShipStat3 ---
SELECT ShipStats2.ShipPercent
FROM ShipStats2
GROUP BY ShipStats2.ShipPercent;

SELECT (SELECT COUNT(*)
FROM [ShipStat3] AS T1
WHERE T1.ShipPercent > T.ShipPercent )+1 AS ShipRank2, T.ShipPercent
FROM ShipStat3 AS T
ORDER BY T.ShipPercent DESC;