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

 

[Phil]

Hey Tom, I have had no time to work on this for the last two weeks. Can
now work on it again. I did delete the original query, (It was quick
and dirty, a little too dirty) so I can't test what you gave me as is.
I am trying to understand what you are doing here. I think I understand
it, AND I think you made a couple of mistakes. Please confirm:

Since I am breaking down my query by Item_Types_Name and Brand_Name,

SELECT *,

(SELECT COUNT(*)
FROM XXX Q1
WHERE Q1.brand_name = Q.brand_name

Above should be WHERE Q1.Item_Types_Name = q.Item_Types_Name
My first group By break

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

I applied the idea to my new and improved queries,and it appears to
work, but there is some behavior I am not sure about.

Rank Type brand QTY
1 Truck Krux 5
3 Truck Krux 4
3 Truck Krux 4
4 Truck Krux 2

Where two items share the same count and have equal footing, they both
get the same rank. I would rather they were ranked 2, then 3. I think
I would just throw a tie breaker in there, which I will try.

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

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:

You seem to have exactly the right idea. You may need a tie breaker. A
query will not "arbitrarily" do ANYTHING AT ALL! It will do only exactly
what you tell it.

Ranking uniquely depends on unique data on which to perform the ranking. As
you can see, the ranking it has done for you is very exactly what it should
do when there is a tie. If two horses finish in first place, the next horse
is in third. It's a 1-1-3 finish.

You're almost certainly correct about the brand_name needing to be
Item_Types_Name. That's for the "group" within which each ranking is
independently performed.

Now, to add a "tie breaker" you will need:

AND (Q1.brand_name < Q.brand_name
OR (Q1.brand_name = Q.brand_name
AND Q1.SumOfship_qty > Q.SumOfshopQty)
OR (Q1.brand_name = Q.brand_name
AND Q1.SumOfship_qty = Q.SumOfshipQty
AND Q1.TieBreaker > Q.TieBreaker))

Heaven help you if you need to rank by more than 5 or 6 columns! Actually,
there's an excellent ANSI standard for comparing two sets of columns like
this, but no one seems to have implemented it. It's really very nice! I
need to look at SQL 2005 to see if it's been done there yet. This is a good
reminder for me!

Tom Ellison

Hey Tom, I have had no time to work on this for the last two weeks. Can
now work on it again. I did delete the original query, (It was quick and
dirty, a little too dirty) so I can't test what you gave me as is. I am
trying to understand what you are doing here. I think I understand it,
AND I think you made a couple of mistakes. Please confirm:

Since I am breaking down my query by Item_Types_Name and Brand_Name,

SELECT *,

(SELECT COUNT(*)
FROM XXX Q1
WHERE Q1.brand_name = Q.brand_name

Above should be WHERE Q1.Item_Types_Name = q.Item_Types_Name
My first group By break

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

I applied the idea to my new and improved queries,and it appears to work,
but there is some behavior I am not sure about.

Rank Type brand QTY
1 Truck Krux 5
3 Truck Krux 4
3 Truck Krux 4
4 Truck Krux 2

Where two items share the same count and have equal footing, they both get
the same rank. I would rather they were ranked 2, then 3. I think I
would just throw a tie breaker in there, which I will try.

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

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.

 

[Phil]

Your example of a tie breaker is exactly whatI had in mind. Thank you
for all the help.

You are a gentlemanand a scholar. You know your stuff, too!

Dear Phil:

You seem to have exactly the right idea. You may need a tie breaker. A
query will not "arbitrarily" do ANYTHING AT ALL! It will do only exactly
what you tell it.

Ranking uniquely depends on unique data on which to perform the ranking. As
you can see, the ranking it has done for you is very exactly what it should
do when there is a tie. If two horses finish in first place, the next horse
is in third. It's a 1-1-3 finish.

You're almost certainly correct about the brand_name needing to be
Item_Types_Name. That's for the "group" within which each ranking is
independently performed.

Now, to add a "tie breaker" you will need:

AND (Q1.brand_name < Q.brand_name
OR (Q1.brand_name = Q.brand_name
AND Q1.SumOfship_qty > Q.SumOfshopQty)
OR (Q1.brand_name = Q.brand_name
AND Q1.SumOfship_qty = Q.SumOfshipQty
AND Q1.TieBreaker > Q.TieBreaker))

Heaven help you if you need to rank by more than 5 or 6 columns! Actually,
there's an excellent ANSI standard for comparing two sets of columns like
this, but no one seems to have implemented it. It's really very nice! I
need to look at SQL 2005 to see if it's been done there yet. This is a good
reminder for me!

Tom Ellison

Hey Tom, I have had no time to work on this for the last two weeks. Can
now work on it again. I did delete the original query, (It was quick and
dirty, a little too dirty) so I can't test what you gave me as is. I am
trying to understand what you are doing here. I think I understand it,
AND I think you made a couple of mistakes. Please confirm:

Since I am breaking down my query by Item_Types_Name and Brand_Name,

SELECT *,

(SELECT COUNT(*)
FROM XXX Q1
WHERE Q1.brand_name = Q.brand_name

Above should be WHERE Q1.Item_Types_Name = q.Item_Types_Name
My first group By break

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

I applied the idea to my new and improved queries,and it appears to work,
but there is some behavior I am not sure about.

Rank Type brand QTY
1 Truck Krux 5
3 Truck Krux 4
3 Truck Krux 4
4 Truck Krux 2

Where two items share the same count and have equal footing, they both get
the same rank. I would rather they were ranked 2, then 3. I think I
would just throw a tie breaker in there, which I will try.



Tom Ellison wrote:

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




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:

I won't try to deny it. Well, at least this. If I didn't think I knew what
I was doing, I wouldn't have the confidence to try to help as I do.

As for being a gentleman, that's in the eye of the beholder.

Tom Ellison

Your example of a tie breaker is exactly whatI had in mind. Thank you for
all the help.

You are a gentlemanand a scholar. You know your stuff, too!

Dear Phil:

You seem to have exactly the right idea. You may need a tie breaker. A
query will not "arbitrarily" do ANYTHING AT ALL! It will do only exactly
what you tell it.

Ranking uniquely depends on unique data on which to perform the ranking.
As you can see, the ranking it has done for you is very exactly what it
should do when there is a tie. If two horses finish in first place, the
next horse is in third. It's a 1-1-3 finish.

You're almost certainly correct about the brand_name needing to be
Item_Types_Name. That's for the "group" within which each ranking is
independently performed.

Now, to add a "tie breaker" you will need:

AND (Q1.brand_name < Q.brand_name
OR (Q1.brand_name = Q.brand_name
AND Q1.SumOfship_qty > Q.SumOfshopQty)
OR (Q1.brand_name = Q.brand_name
AND Q1.SumOfship_qty = Q.SumOfshipQty
AND Q1.TieBreaker > Q.TieBreaker))

Heaven help you if you need to rank by more than 5 or 6 columns!
Actually, there's an excellent ANSI standard for comparing two sets of
columns like this, but no one seems to have implemented it. It's really
very nice! I need to look at SQL 2005 to see if it's been done there
yet. This is a good reminder for me!

Tom Ellison

Hey Tom, I have had no time to work on this for the last two weeks. Can
now work on it again. I did delete the original query, (It was quick and
dirty, a little too dirty) so I can't test what you gave me as is. I am
trying to understand what you are doing here. I think I understand it,
AND I think you made a couple of mistakes. Please confirm:

Since I am breaking down my query by Item_Types_Name and Brand_Name,

SELECT *,

(SELECT COUNT(*)
FROM XXX Q1
WHERE Q1.brand_name = Q.brand_name

Above should be WHERE Q1.Item_Types_Name = q.Item_Types_Name
My first group By break

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


I applied the idea to my new and improved queries,and it appears to work,
but there is some behavior I am not sure about.

Rank Type brand QTY
1 Truck Krux 5
3 Truck Krux 4
3 Truck Krux 4
4 Truck Krux 2

Where two items share the same count and have equal footing, they both
get the same rank. I would rather they were ranked 2, then 3. I think I
would just throw a tie breaker in there, which I will try.



Tom Ellison wrote:


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




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.