How create ranking within subgroup

[Ben]

Hi all,

I have a SQL question. I have a table which contain fields:

Product, Customer and SpendByCustomer

What I would like is to create a query of the top spend by customers
(aggregated) by Product. I would like the query list by Product, then by
the top spend customer (in descending order) and a new field that would
tag it as such. For example:


Product Customer Spend Order
1 ABC 1000 5
1 CDE 500 4
1 FGH 400 3
1 IJK 300 2
1 LMN 100 1
2 FGH 400 3
2 CDE 100 2
2 ABC 50 1

The value of the Order field is based on number of customers for that
particular product. Within that sub-group of customer for that
particular product, the value of Order is sorted by the top spend of
that customer for that product.


Can you please help me with some ideas/ SQL code snippets?

Thanks in advance,

Ben

 

[vanderghast]

Many solutions. One using join could be:


SELECT a.productID, a.Customer, a.Spend, COUNT(*)
FROM tableName AS a INNER JOIN tableName AS b
ON a.productID = b.productID
AND ( a.spend < b.spend
OR (a.spend = b.spend AND a.pk >= b.pk))
GROUP BY a.productID, a.Customer, a.Spend
ORDER BY a.productID, a.Spend DESC, a.pk


Note that I used a field name pk, it is your primary key taht I used to
break equality.


Vanderghast, Access MVP

 

[KARL DEWEY]

I was hung up until I read your post about using PK.
SELECT Q.Product, Q.Customer, Q.SpendByCustomer, (SELECT COUNT(*) FROM
[Tbl_Ben] Q1
WHERE Q1.[Product] = Q.[Product] AND Q1.PK >= Q.PK) AS Rank
FROM Tbl_Ben AS Q
ORDER BY Q.Product, Q.SpendByCustomer DESC;

 

[Ben]

Karl, Vanderghast -

Is there a way to write either of your queries via some other JOIN? My
experience is that subquery takes a long time especially with a large
dataset and it looks like is taking a long time to run.

Any suggestions?

Thanks in advance,

Ben

 

[vanderghast]

The first formulation uses a join. Be sure to start with the condition on
productID and that productID is indexed. It may still takes some times with
Jet since non-equi joins, in Jet, are not as well optimized as they are for
equi-joins.


If it is too slow, you can still try to append the (relevant) columns to a
new table having an autonumber key. Append the data with an ORDER BY clause:

INSERT INTO temp(product, customer, spend)
SELECT product, customer, spend
FROM tableName
ORDER BY product, customer, spend


This operation is generally relatively very fast.

You thus get, in the autonumber column of temp, a rank over all the tables,
NOT by group, but that is easy to fix: it is a matter to subtract
MIN(rank)-1. If rank is the autonumber field name:


SELECT product, MIN(rank)-1 AS offset
FROM temp
GROUP BY product

saved as q1, then


SELECT *, temp.rank-q1.offset AS rankByGroup
FROM temp INNER JOIN q1
ON temp.product=q1.product



should return the rank, by group of product.


Vanderghast, Access MVP