Ranking with group by?

[Phil]

I can, in a report, generate a sequential number for each detail record,
and reset that number at each subtotal break.

Best Vehicles
CARS
1 Toyota
2 Ford
3 VW

Trucks
1 Dodge
2 Ford
3 Chevy


How do I get the same results in a query?

Thanx

 

[Tom Ellison]

Dear Phil:

I typically use a subquery to count the rows prior to the current row within
the current group.

With a little more information I can provide this. Please tell me what
column(s) are the group, where the sequence number resets. Provide a query
showing all the columns you want to see with the exception of this running
count, what we often call a Rank. I'll add that column to your query in a
subsequent reply.

Tom Ellison

 

[Phil]

SELECT t2.item_types_name, TOP5UnionStep1b.brand_name,
TOP5UnionStep1b.[Customer# AS #], customer.name,
TOP5UnionStep1b.SumOfship_qty
FROM customer INNER JOIN (t2 INNER JOIN TOP5UnionStep1b ON t2.Category =
TOP5UnionStep1b.Category) ON customer.customer_id =
TOP5UnionStep1b.[Customer# AS #]
WHERE (((TOP5UnionStep1b.SumOfship_qty) In (Select Top 15
[TOP5UnionStep1b]![SumOfship_qty] From [TOP5UnionStep1b] where
[TOP5UnionStep1b]![Category] = [T2]![Category] Order By
[TOP5UnionStep1b]![SumOfship_qty] Desc)))
ORDER BY t2.item_types_name, TOP5UnionStep1b.brand_name,
TOP5UnionStep1b.SumOfship_qty DESC;

This will give 5 coloums,
item_types_name, brand_name, Customer# AS #, name, SumOfship_qty

And will break at Brand_name.

 

[Tom Ellison]

Dear Phil:

SELECT t2.item_types_name, TOP5UnionStep1b.brand_name,
TOP5UnionStep1b.[Customer# AS #], C.name,
TOP5UnionStep1b.SumOfship_qty
FROM customer C
INNER JOIN (t2 INNER JOIN TOP5UnionStep1b T5
ON t2.Category = T5.Category)
ON C.customer_id = T5.[Customer# AS #]
WHERE (((T5.SumOfship_qty) In (
Select Top 15 [TOP5UnionStep1b]![SumOfship_qty]
From [TOP5UnionStep1b]
where [TOP5UnionStep1b]![Category] = [T2]![Category]
Order By [TOP5UnionStep1b]![SumOfship_qty] DESC)))
ORDER BY t2.item_types_name, T5.brand_name, T5.SumOfship_qty DESC;

Above, I've reformatted your query for my readability and added some
aliases. This is mostly to make it easier for me to study.

To add the rank, I recommend trying you keep the above as is, and add a
query referencing this query. I'll assume the above query you already have
is a query named XXX.

Also, I must know the order in which the ranking is to be done. I'm
assuming it is the ordering you specified in your query, less the
item_types_name which is the group. So, the rank will be based on
brand_name and SumOfship_qty, the latter descending.

SELECT *,
(SELECT COUNT(*)
FROM XXX Q1
WHERE Q1.brand_name = Q.brand_name
AND (Q1.brand_name < Q.brand_name
OR (Q1.brand_name = Q.brand_name
AND Q1.SumOfship_qty > Q.SumOfshopQty))
AS Rank
FROM XXX Q
ORDER BY item_types_name, brand_name, SumOfship_qty DESC

Since your query is a JOIN of 3 tables (or queries) and the rank uses values
from 2 of those 3, I have deemed it best not to combine this into one query.
This is simpler, and should work about as well.

Now, there's a lot of work involved in counting up all the rows this way,
and it may not be fast. Doing it in a report uses completely different
methods, and will be much quicker.

Please let me know how this works for you, and if I can be of any other
assistance.

Tom Ellison