Ranking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have read many posts on this site but cannot find a support to my inquiry.

I have a table named tbl By Category with the following fields:
Category
Subcategory
Supplier
Spend

I need to rank Suppliers by Spend, within the groups:Subcategory and
Category. In other words, I need to have the rank re-stating every time the
Subcategory changes.

Can anybody help?
Thank you



Table: NFL Team Sls

Store Sls Team Rnk
a 100 Eagles 1
a 150 Patriots 2
b 75 Eagles 1
b 300 Patriots 2
c 125 Eagles 1
c 250 Patriots 2


This is what I am looking for.

Note the Rank field is not on the table...I tried to use the following SQL,
but it ranks everyone.
 
Hi,


You did not supply the SQL statement.

SELECT a.f1, a.f2, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.f1=b.f1 AND a.f2 >= b.f2
GROUP BY f1, f2


will rank for each group f1, accordingly to the value in f2

SELECT a.f1, a.f2, a.f3, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.f1=b.f1 AND a.f2=b.f2 AND a.f3 >= b.f3
GROUP BY f1, f2, f3


will rank for each group f1, f2, accordingly to the value in f3




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top