Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1285
  • Last Modified:

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

0
mleeman
Asked:
mleeman
  • 3
  • 2
1 Solution
 
chapmandewCommented:
Is there an autoincrement column on your table?
0
 
mleemanAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mleemanAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
Then you could use an in-between query to create a distinct list of TotalSales:

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

SELECT DISTINCT
  TotalSales
FROM
  qryProductTotals

Save this as qryProductTotalsDistinct.
Then:

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

/gustav
0
 
mleemanAuthor Commented:
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.

0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now