asked on

Access Ranking Query results in 1st place tie showing as 2nd place ties

I have used the following code from MSDN to create a ranking query. But when I change the data to show 2 first place ties, the query assigns rank position 2 to both ties, not 1 to both:

Product Name      TotalSales      Rank
Camembert Pierrot      1496      2
Raclette Courdavault      1496      2
Gorgonzola Telino      1397      3
Gnocchi di nonna Alice      1263      4
Pavlova      1158      5
Rhönbräu Klosterbier      1155      6

I need both 1st place ties to show as rank 1.

CODE:
SELECT ProductName,
SUM(Quantity) AS TotalSales
FROM [Order Details] INNER JOIN Products
ON [Order Details].ProductID =
Products.ProductID
GROUP BY ProductName
ORDER BY SUM(Quantity) DESC

After that query has been saved as qryProductTotals, you can use this query with a subquery to generate the sales rankings:

SELECT ProductName, TotalSales,
(SELECT COUNT(*)
FROM qryProductTotals AS QPT
WHERE qryProductTotals.TotalSales
<= QPT.TotalSales) AS Rank
FROM qryProductTotals
ORDER BY TotalSales DESC
from page: http://msdn.microsoft.com/en-us/library/aa217680(office.11).aspx

chapmandew

Is there an autoincrement column on your table?

mleeman

ASKER

The data is from the sample database downloed from microsoft.

Products table does have ProductID field set to Autonumber. The data is from the MS Access sample northwind database

Gustav Brock

How about:

SELECT ProductName, TotalSales,
(SELECT COUNT(*)
FROM qryProductTotals AS QPT
WHERE qryProductTotals.TotalSales
< QPT.TotalSales) + 1 AS Rank
FROM qryProductTotals
ORDER BY TotalSales DESC

/gustav

mleeman

ASKER

When I did that, it did fix the 2 first places, but then it bumped 2nd place to 3rd. With this query if there is not a tie the 1-3 works fine, but if 1st or 2nd is tied, the rank following a tie is also bumped up +1.

Product Name      TotalSales      Rank
Camembert Pierrot      1496      1
Raclette Courdavault      1496      1
Gorgonzola Telino      1397      3
Gnocchi di nonna Alice      1263      4
Pavlova      1158      5

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mleeman

ASKER

When did that everything ranked as 70.

I did more research on MS KB 208946 and got it to work.

Qry1:
SELECT Products.ProductName, Sum([Order Details].Quantity) AS TotalSales
FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
GROUP BY Products.ProductName
ORDER BY Sum([Order Details].Quantity) DESC;

Qry2:
SELECT DISTINCT Count(qry1.ProductName) AS CountOfProductName, qry1.TotalSales
FROM qry1
GROUP BY qry1.TotalSales
ORDER BY qry1.TotalSales DESC;

Qry3:
SELECT [qry1-alias].ProductName, [qry1-alias].TotalSales, (Select Count(*) from [qry2] Where
[TotalSales] > [qry1-Alias].[TotalSales])+1 AS Ranking2
FROM qry1 AS [qry1-alias]
ORDER BY [qry1-alias].TotalSales DESC;

Result:
Product Name      TotalSales      Ranking2
Camembert Pierrot      1496      1
Raclette Courdavault      1496      1
Gorgonzola Telino      1496      1
Gnocchi di nonna Alice      1263      2
Pavlova      1158      3
Rhönbräu Klosterbier      1155      4
Guaraná Fantástica      1125      5
Boston Crab Meat      1103      6

Thank you for the help, you got me on the right track.