Allen Browne Ranking Sample?

[kwooten]

Was wondering if anyone had a sample query like Allen Browne is doing.. i follow much better if i can see it.



http://allenbrowne.com/ranking.html

[Dal Jeanis]

Sample for which one? Browne has the sample queries built, both visually and with SQL, in the middle of the page you cite, under "Ranking Queries".

Browne, Stephen Lebans and/or Peter Schroeder also posted a zipped VBA version here http://www.lebans.com/rownumber.htm

I'll walk you through the method.

First, create a work table or query (qry1) that has two fields, the Primary Key (PK) and the Order Criteria (OC). It doesn't matter whether the order criteria is a dollar amount, a GPA, or a text field you are sorting into alpha order. You can have other fields as well, but those two are necessary for the method.

In Browne's example, the query is qryCustomerValue (qry1), returning fields CustomerID (PK) and TotalValue (OC).

Second, create a second query (qry2) based on the first query, returning three values: The same Primary Key (PK), the same Order Criteria (OC), and the ordinal rank (OR), which is a subselect that counts the number of records from the same query that beat that record by its order criteria. Depending on your purposes, you can count the number that are less than that record, or the number that are greater than that record. Depending on your purposes, you can add or subtract 1, so that the highest record gets a rank of 1 or the total number of records in the file, as appropriate.

In Browne's example, the second query is qryCustomerValueRank (qry2), returning fields CustomerID (PK) and TotalValue (OC) and BeatenBy (OR). Here's his code for comparison.

SELECT qryCustomerValue.CustomerID,
qryCustomerValue.TotalValue,
  (SELECT Count([CustomerID]) AS HowMany
  FROM qryCustomerValue AS Dupe
  WHERE Dupe.TotalValue > qryCustomerValue.TotalValue)
AS BeatenBy
FROM qryCustomerValue;

Some things to note about the example -

1) It doesn't much matter what field you choose to COUNT in the subselect, as long as it is never NULL among the records you care about. The Primary Key is a good default.

2) I don't know of a reason to alias the COUNT inside the subselect and then alias it again outside, but it should work as Browne coded it. My general form (below) only does that rename once, but be aware that it's aircode, and in any dispute between me and Jet, Jet wins.

3) The subselect is checking each record in the query against ANOTHER copy of the same query, so the second copy of qry1 must be aliased (renamed) in the subselect.

4) The rank Browne is creating is the number of records which have a higher Value than the current record. My general form, provided below, assumes that OC is an ascending value, like a customer name field, and that you want the first-sorted record to receive the value 1 rather than zero.

5) The general form of this second query is

SELECT qry1.PK, qry1.OC, 
  (SELECT Count(PK) + 1
   FROM qry1 AS QD
   WHERE QD.OC < qry1.OC) AS OR
FROM qry1;

6) This general form, like Browne's, is intended to give the same rank to any records that tie. If you want each record to have a unique rank, then you can use the PK (or any other unique field) as a tiebreaker in the subselect WHERE clause, like so:

  WHERE ( QD.OC < qry1.OC OR
         (QD.OC = qry1.OC AND 
          QD.PK < qry1.PK) )

In that case, you could also make it QD.PK <= qry1.PK and get rid of the +1 from the COUNT.