Second max and third max

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

Guest

Dear developers – I have a problem that I can’t find the solutions for.
I have a table with products. It is simple to find the max value, but is it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for all the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than’s in advance

Stefan
 
Dear Stefan:

I notice first of all that you use the value Apple / 20 only once even
though it appears twice. So, I'll base this on a DISTINCT query of the
table.

As a first part of the solution, I offer this:

SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value)
AS Rank
FROM
(SELECT DISTINCT Product, Value FROM Product) AS T
WHERE
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC

This gives a ranking to each Product / Value combination.

Unfortunately, you cannot make a crosstab of this. Maybe this should be
fixed. So, dump it into a table:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value FROM Product) AS T
WHERE (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Make the crosstab on table Temp (you must create this table manually before
using. Make the Rank column Integer).

TRANSFORM Max(Value) AS MValue
SELECT Product
FROM Temp
GROUP BY Product
PIVOT Rank;

Now, I don't get the same results. Did you make a mistake? Oranges are 9,
8, and 7, right? Not 9, 7, and 5!

Tom Ellison
 
Hi


Rank your data, something like:


SELECT a.Product, a.[Value], COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.product = b.product
AND a.Value >= b.Value
GROUP BY a.Product, a.[Value]
HAVING COUNT(*) <= 3



where the magic number 3 can be a parameter giving the (maximum) number of
values you wish, per product.


Hoping it may help,
Vanderghast, Access MVP
 
Dear Stefan:

I see I made a mistake in not filtering to a single country. A modification
is on the way.

Tom Ellison


Tom Ellison said:
Dear Stefan:

I notice first of all that you use the value Apple / 20 only once even
though it appears twice. So, I'll base this on a DISTINCT query of the
table.

As a first part of the solution, I offer this:

SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value)
AS Rank
FROM
(SELECT DISTINCT Product, Value FROM Product) AS T
WHERE
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC

This gives a ranking to each Product / Value combination.

Unfortunately, you cannot make a crosstab of this. Maybe this should be
fixed. So, dump it into a table:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value FROM Product) AS T
WHERE (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Make the crosstab on table Temp (you must create this table manually
before using. Make the Rank column Integer).

TRANSFORM Max(Value) AS MValue
SELECT Product
FROM Temp
GROUP BY Product
PIVOT Rank;

Now, I don't get the same results. Did you make a mistake? Oranges are
9, 8, and 7, right? Not 9, 7, and 5!

Tom Ellison


Stefan said:
Dear developers - I have a problem that I can't find the solutions for.
I have a table with products. It is simple to find the max value, but is
it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for all
the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than's in advance

Stefan
Click to expand...
Click to expand...
 
Dear Stefan:

Here's the modified first query:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Country = T.Country
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T
WHERE Country = "Spain"
AND (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Country = T.Country
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Tom Ellison


Tom Ellison said:
Dear Stefan:

I see I made a mistake in not filtering to a single country. A
modification is on the way.

Tom Ellison


Tom Ellison said:
Dear Stefan:

I notice first of all that you use the value Apple / 20 only once even
though it appears twice. So, I'll base this on a DISTINCT query of the
table.

As a first part of the solution, I offer this:

SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value)
AS Rank
FROM
(SELECT DISTINCT Product, Value FROM Product) AS T
WHERE
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC

This gives a ranking to each Product / Value combination.

Unfortunately, you cannot make a crosstab of this. Maybe this should be
fixed. So, dump it into a table:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value FROM Product) AS T
WHERE (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Make the crosstab on table Temp (you must create this table manually
before using. Make the Rank column Integer).

TRANSFORM Max(Value) AS MValue
SELECT Product
FROM Temp
GROUP BY Product
PIVOT Rank;

Now, I don't get the same results. Did you make a mistake? Oranges are
9, 8, and 7, right? Not 9, 7, and 5!

Tom Ellison


Stefan said:
Dear developers - I have a problem that I can't find the solutions for.
I have a table with products. It is simple to find the max value, but is
it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for all
the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than's in advance

Stefan
Click to expand...
Click to expand...
Click to expand...
 
.... use <=, not >=. With >=, you get ASCending ordering for your rank, with
<=, you get DESCending ordering.


Vanderghast, Access MVP

Michel Walsh said:
Hi


Rank your data, something like:


SELECT a.Product, a.[Value], COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.product = b.product
AND a.Value >= b.Value
GROUP BY a.Product, a.[Value]
HAVING COUNT(*) <= 3



where the magic number 3 can be a parameter giving the (maximum) number of
values you wish, per product.


Hoping it may help,
Vanderghast, Access MVP


Stefan said:
Dear developers - I have a problem that I can't find the solutions for.
I have a table with products. It is simple to find the max value, but is
it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for all
the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than's in advance

Stefan
Click to expand...
Click to expand...
 
Dear Stefan:

By the way, I assumed a table name of Product, so you may have to change the
query to use the proper table name if that is not it. When you post a
question, including the correct table name and column names is helpful.
Then you won't have so many things to "fix up" when someone posts a proposed
solution.

Tom Ellison

Tom Ellison said:
Dear Stefan:

Here's the modified first query:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Country = T.Country
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T
WHERE Country = "Spain"
AND (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Country = T.Country
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Tom Ellison


Tom Ellison said:
Dear Stefan:

I see I made a mistake in not filtering to a single country. A
modification is on the way.

Tom Ellison


Tom Ellison said:
Dear Stefan:

I notice first of all that you use the value Apple / 20 only once even
though it appears twice. So, I'll base this on a DISTINCT query of the
table.

As a first part of the solution, I offer this:

SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value)
AS Rank
FROM
(SELECT DISTINCT Product, Value FROM Product) AS T
WHERE
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC

This gives a ranking to each Product / Value combination.

Unfortunately, you cannot make a crosstab of this. Maybe this should be
fixed. So, dump it into a table:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value FROM Product) AS T
WHERE (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Make the crosstab on table Temp (you must create this table manually
before using. Make the Rank column Integer).

TRANSFORM Max(Value) AS MValue
SELECT Product
FROM Temp
GROUP BY Product
PIVOT Rank;

Now, I don't get the same results. Did you make a mistake? Oranges are
9, 8, and 7, right? Not 9, 7, and 5!

Tom Ellison


Dear developers - I have a problem that I can't find the solutions for.
I have a table with products. It is simple to find the max value, but
is it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for
all the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than's in advance

Stefan
Click to expand...
Click to expand...
Click to expand...
 
Dear Michael and Tom
Thanks for your replies. I like both of your solutions. But…

Michael
Your solution works fine, but when I have a duplicate value in the table I
receive the wrong answer. Only one value of is shown in the query. Is that
correct?

Tom
It works great, and I like your solution with inserting it to a table. Maybe
I will make a cross table or Pivot table from the temp-table.

Excel has a function called LARGE. It returns the Nth value from a list
=LARGE(A1:A10,3) returns the third largest value from the list. Would be
easier to use a user defined function in the query to return the Nth value?
Is it possible? I have search the web for a similar function in Access, but
haven’t found anything.

Once more, Thank’s for your advice and input.

Stefan


Michel Walsh said:
.... use <=, not >=. With >=, you get ASCending ordering for your rank, with
<=, you get DESCending ordering.


Vanderghast, Access MVP

Michel Walsh said:
Hi


Rank your data, something like:


SELECT a.Product, a.[Value], COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.product = b.product
AND a.Value >= b.Value
GROUP BY a.Product, a.[Value]
HAVING COUNT(*) <= 3



where the magic number 3 can be a parameter giving the (maximum) number of
values you wish, per product.


Hoping it may help,
Vanderghast, Access MVP


Stefan said:
Dear developers - I have a problem that I can't find the solutions for.
I have a table with products. It is simple to find the max value, but is
it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for all
the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than's in advance

Stefan
Click to expand...
Click to expand...
Click to expand...
 
Dear Michael and Tom
Thanks for your replies. I like both of your solutions. But…

Michael
Your solution works fine, but when I have a duplicate value in the table I
receive the wrong answer. Only one value of is shown in the query. Is that
correct?

Tom
It works great, and I like your solution with inserting it to a table. Maybe
I will make a cross table or Pivot table from the temp-table.

Excel has a function called LARGE. It returns the Nth value from a list
=LARGE(A1:A10,3) returns the third largest value from the list. Would be
easier to use a user defined function in the query to return the Nth value?
Is it possible? I have search the web for a similar function in Access, but
haven’t found anything.

Once more, Thank’s for your advice and input.

Stefan


Tom Ellison said:
Dear Stefan:

By the way, I assumed a table name of Product, so you may have to change the
query to use the proper table name if that is not it. When you post a
question, including the correct table name and column names is helpful.
Then you won't have so many things to "fix up" when someone posts a proposed
solution.

Tom Ellison

Tom Ellison said:
Dear Stefan:

Here's the modified first query:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Country = T.Country
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T
WHERE Country = "Spain"
AND (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value, Country FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Country = T.Country
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Tom Ellison


Tom Ellison said:
Dear Stefan:

I see I made a mistake in not filtering to a single country. A
modification is on the way.

Tom Ellison


Dear Stefan:

I notice first of all that you use the value Apple / 20 only once even
though it appears twice. So, I'll base this on a DISTINCT query of the
table.

As a first part of the solution, I offer this:

SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value)
AS Rank
FROM
(SELECT DISTINCT Product, Value FROM Product) AS T
WHERE
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC

This gives a ranking to each Product / Value combination.

Unfortunately, you cannot make a crosstab of this. Maybe this should be
fixed. So, dump it into a table:

INSERT INTO Temp (Product, [Value], Rank)
SELECT Product, Value,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) AS Rank
FROM (SELECT DISTINCT Product, Value FROM Product) AS T
WHERE (SELECT COUNT(*)
FROM (SELECT DISTINCT Product, Value FROM Product) T1
WHERE T1.Product = T.Product
AND T1.Value > T.Value) < 3
ORDER BY Product, Value DESC;

Make the crosstab on table Temp (you must create this table manually
before using. Make the Rank column Integer).

TRANSFORM Max(Value) AS MValue
SELECT Product
FROM Temp
GROUP BY Product
PIVOT Rank;

Now, I don't get the same results. Did you make a mistake? Oranges are
9, 8, and 7, right? Not 9, 7, and 5!

Tom Ellison


Dear developers - I have a problem that I can't find the solutions for.
I have a table with products. It is simple to find the max value, but
is it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for
all the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than's in advance

Stefan
Click to expand...
Click to expand...
Click to expand...
 
Hi,


You can make a unique rank by adding a comparison with the primary key (you
have one?):

instead of

ON a.product = b.product
AND a.Value <= b.Value


try
ON a.product = b.product
AND ( a.Value < b.Value OR (a.Value = b.Value AND a.pk <= b.pk))


and the primary key break any equality.


Otherwise, if there are 2 values in position 3, the ranks will be 1, 2,
4, 4, 5, 6, 7....

if you want 1, 2, 3, 3, 5, 6, 7 ... you can use:

SELECT a.Product, a.[Value], 1+COUNT(b.Value) As Rank
FROM myTable As a LEFT JOIN myTable As b
ON a.product = b.product
AND a.Value < b.Value
GROUP BY a.Product, a.[Value]





Hoping it may help,
Vanderghast, Access MVP


Stefan said:
Dear Michael and Tom
Thanks for your replies. I like both of your solutions. But.

Michael
Your solution works fine, but when I have a duplicate value in the table I
receive the wrong answer. Only one value of is shown in the query. Is that
correct?

Tom
It works great, and I like your solution with inserting it to a table.
Maybe
I will make a cross table or Pivot table from the temp-table.

Excel has a function called LARGE. It returns the Nth value from a list
=LARGE(A1:A10,3) returns the third largest value from the list. Would be
easier to use a user defined function in the query to return the Nth
value?
Is it possible? I have search the web for a similar function in Access,
but
haven't found anything.

Once more, Thank's for your advice and input.

Stefan


Michel Walsh said:
.... use <=, not >=. With >=, you get ASCending ordering for your rank,
with
<=, you get DESCending ordering.


Vanderghast, Access MVP

Michel Walsh said:
Hi


Rank your data, something like:


SELECT a.Product, a.[Value], COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.product = b.product
AND a.Value >= b.Value
GROUP BY a.Product, a.[Value]
HAVING COUNT(*) <= 3



where the magic number 3 can be a parameter giving the (maximum) number
of
values you wish, per product.


Hoping it may help,
Vanderghast, Access MVP


Dear developers - I have a problem that I can't find the solutions
for.
I have a table with products. It is simple to find the max value, but
is
it
possible to find the max, second max and third max values for all the
products in one country?

This is a sample table with the products: (There are also ProdID for
all
the
products.)
Product Value Country
Apple 20 Spain
Apple 19 Spain
Apple 20 Spain
Apple 18 Israel
Apple 21 Spain
Orange 8 Greece
Orange 4 Spain
Orange 5 Spain
Orange 7 Spain
Orange 9 Spain

The query would generate the following answered from Spain:
Product Max 2Max 3Max
Apple 21 20 19
Orange 9 7 5

Is this possible?

Than's in advance

Stefan
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

max 7
Avg, Min, Max of an Array 1
Pivot Table - Problems when using Calculated Item 1
Consolidate Lists from Multiple Sheets on another Sheet 9
Not sure what function, macro, etc to use 2
Converting a report into a Pivot Table format 1
Returning values to unique rows 3
Match and return functions 14

Back
Top