Getting Max of a Column And Displaying its Corresponding Value from Other Column

[NickFox]

Dear Experts,

I am trying to build a query that gives me the Max of a [Price] but shows only the [Rate] corresponding to the maximum value. I have found this on internet but cant figure out the way to use this. Kindly help me with this.





Here are a few screenshot of what i am dealing with.



Regards,

Usmaan.

[June7]

Consider:

SELECT Table1.* FROM Table1 INNER JOIN
(SELECT ItemName, Type, Colour, Size, Thickness, Max(Price) AS MaxPrice FROM Table1
GROUP BY ItemName, Type, Colour, Size, Thickness) AS Dupe
ON Table1.Price = Dupe.MaxPrice AND Table1.Thickness = Dupe.Thickness
AND Table1.Size = Dupe.Size AND Table1.Colour = Dupe.Colour
AND Table1.Type = Dupe.Type AND Table1.ItemName = Dupe.ItemName;

[NickFox]

Consider:

SELECT Table1.* FROM Table1 INNER JOIN
(SELECT ItemName, Type, Colour, Size, Thickness, Max(Price) AS MaxPrice FROM Table1
GROUP BY ItemName, Type, Colour, Size, Thickness) AS Dupe
ON Table1.Price = Dupe.MaxPrice AND Table1.Thickness = Dupe.Thickness
AND Table1.Size = Dupe.Size AND Table1.Colour = Dupe.Colour
AND Table1.Type = Dupe.Type AND Table1.ItemName = Dupe.ItemName;

Thank you so much for your reply. Pardon my ignorance. Where do i need to put this code in. A little more explanation will help a great deal.

[aytee111]

Create a new query and copy and paste June's SQL into it under SQL View. Then save it.

[ssanfu]

Also, be aware that "Rate", "Size" and "Type" are reserved words and shouldn't be used as object names.
"Rate" is a built in function
"Size" is a JET/ACE reserved word
"Type" is a field property

Plus the words are not very descriptive. I would suggest "ItemRate", "ItemSize" and "ItemType"...

[NickFox]

Consider:

SELECT Table1.* FROM Table1 INNER JOIN
(SELECT ItemName, Type, Colour, Size, Thickness, Max(Price) AS MaxPrice FROM Table1
GROUP BY ItemName, Type, Colour, Size, Thickness) AS Dupe
ON Table1.Price = Dupe.MaxPrice AND Table1.Thickness = Dupe.Thickness
AND Table1.Size = Dupe.Size AND Table1.Colour = Dupe.Colour
AND Table1.Type = Dupe.Type AND Table1.ItemName = Dupe.ItemName;

June Thanks for your code. It does give the maximum price but a small little thing is missing. The items differ in Type, Colour, Size and Thickness and so as their prices. The code doesnt take into account these factors and only give the maximum price of an item regardless of Type, Colour, Size or Thickness.

The simple query that i managed to build gives everything but i need to get the respective Rate for the Maximum Price of an item and not to Group by Rate. Grouping by Rate kills the purpose of getting the maximum price of an item with certain Type, Colour and Thickness.

The first Screenshot show a piece of code that i guess might do the trick but i don't how to put the filter in where clause. Do i have to write this criteria under the Rate field or is there any other method.

Kindly help me a bit. I am grateful for your time and effort.

Cheers.

[NickFox]

Create a new query and copy and paste June's SQL into it under SQL View. Then save it.

Thanks for the guidance.

[NickFox]

Also, be aware that "Rate", "Size" and "Type" are reserved words and shouldn't be used as object names.
"Rate" is a built in function
"Size" is a JET/ACE reserved word
"Type" is a field property

Plus the words are not very descriptive. I would suggest "ItemRate", "ItemSize" and "ItemType"...

Thanks for your suggestion. I am gonna take care of this in future. Cheers.

[June7]

The inner query is retrieving max price associated with each ItemName/Type/Colour/Size/Thickness combination. Then that dataset is joined to the table linking on ItemName/Type/Colour/Size/Thickness/Prices to return the Rate associated with that max price. That's what I see as the desired output in the example.

[NickFox]

The inner query is retrieving max price associated with each ItemName/Type/Colour/Size/Thickness combination. Then that dataset is joined to the table linking on ItemName/Type/Colour/Size/Thickness/Prices to return the Rate associated with that max price. That's what I see as the desired output in the example.

Please Check this one June.

[June7]

I built a table that has just 2 records - the two you identified as the issue. My query returns only 1 record. So I just noticed other records with the same ItemName/Type/Colour/Size/Thickness combination. Why would you not want the 106.44 record from the original example?

[NickFox]

I built a table that has just 2 records - the two you identified as the issue. My query returns only 1 record. So I just noticed other records with the same ItemName/Type/Colour/Size/Thickness combination. Why would you not want the 106.44 record from the original example?

Thank you June for replying. Actually 106.44 is what it should give being the Maxprice. Your Code indeed returns 106.44. but for some other items (as shown at the end of previous screenshot) it gives three records all with same combination and also some of the Items are also missing (present in the parent table but not appearing in query)

[June7]

Expanded dataset to:

ItemName	Type	Colour	Size	Thickness	Rate	Price
100x50 Rec Tube	S	MF	6	2	10.4	98.4
100x50 Rec Tube	S	MF	6	2	10.5	99.35
100x50 Rec Tube	S	MF	6	2		100
30x30 Eq Angle	S	MF	6	3		30.21
30x30 Eq Angle	S	MF	6	3	10.5	29.043
30x30 Eq Angle	S	MF	6	3	10.75	29.73
100x50 Rec Tube	S	MF	6	2	10.75	101.71
100x50 Rec Tube	S	MF	6	2	11.25	106.44

Modified query:
SELECT Table1.* FROM Table1 INNER JOIN
(SELECT ItemName, Type, Colour, Size, Thickness, Max(Price) AS MaxPrice FROM Table1 WHERE Not Rate Is Null
GROUP BY ItemName, Type, Colour, Size, Thickness) AS Dupe
ON Table1.Price = Dupe.MaxPrice AND Table1.Thickness = Dupe.Thickness
AND Table1.Size = Dupe.Size AND Table1.Colour = Dupe.Colour
AND Table1.Type = Dupe.Type AND Table1.ItemName = Dupe.ItemName;

Output:

ItemName	Type	Colour	Size	Thickness	Rate	Price
30x30 Eq Angle	S	MF	6	3	10.75	29.73
100x50 Rec Tube	S	MF	6	2	11.25	106.44

Provide a representative dataset. Paste table into post or provide db. Follow instructions at bottom of my post.

[NickFox]

Expanded dataset to:

ItemName	Type	Colour	Size	Thickness	Rate	Price
100x50 Rec Tube	S	MF	6	2	10.4	98.4
100x50 Rec Tube	S	MF	6	2	10.5	99.35
100x50 Rec Tube	S	MF	6	2		100
30x30 Eq Angle	S	MF	6	3		30.21
30x30 Eq Angle	S	MF	6	3	10.5	29.043
30x30 Eq Angle	S	MF	6	3	10.75	29.73
100x50 Rec Tube	S	MF	6	2	10.75	101.71
100x50 Rec Tube	S	MF	6	2	11.25	106.44

Modified query:
SELECT Table1.* FROM Table1 INNER JOIN
(SELECT ItemName, Type, Colour, Size, Thickness, Max(Price) AS MaxPrice FROM Table1 WHERE Not Rate Is Null
GROUP BY ItemName, Type, Colour, Size, Thickness) AS Dupe
ON Table1.Price = Dupe.MaxPrice AND Table1.Thickness = Dupe.Thickness
AND Table1.Size = Dupe.Size AND Table1.Colour = Dupe.Colour
AND Table1.Type = Dupe.Type AND Table1.ItemName = Dupe.ItemName;

Output:

ItemName	Type	Colour	Size	Thickness	Rate	Price
30x30 Eq Angle	S	MF	6	3	10.75	29.73
100x50 Rec Tube	S	MF	6	2	11.25	106.44

Provide a representative dataset. Paste table into post or provide db. Follow instructions at bottom of my post.

Dear June, Here are the Tables. I have exported them to Excel.

My Goal is to built a Query that gives me the Maximum Price for each Item (166 of them).

1). I need to Group it by ITEM NAME, TYPE, SIZE, THICKNESS to find the MAX PRICE.
2). If the MAX PRICE has a RATE mentioned in the table. It should also appear in query.

PROBLEM
The problem is that the RATE varies a lot for each Item. So when i Select (Max) in (Totals) for MAX PRICE. It mentions Max Price of all RATES. Thus killing the purpose of having a Max PRICE.
MAX PRICE must only have Rate (if present) mentioned for the highest price of an item.

I hope it gives you a good idea of the problem. Thank you for your time.

[NickFox]

ID	ItemName	VendorName	DatePurchased	Type	Size	Colour	Thickness	Rate	Price	Details
1593	100x20 Rec Tube	SH	29/01/2016	S	6	MF	2	11.25	84.57
1780	100x40 Rec Tube	SH	23/05/2017	S	6	MF	2	10.75	94.75
1535	100x50 Rec Tube	SH	06/06/2016	S	6	MF	2.5	10.5	123.35
1589	100x50 Rec Tube	SH	26/01/2016	S	6	MF	2	11.25	106.44
1766	100x50 Rec Tube	SH	14/05/2017	S	6	MF	3	10.75	150.47
2008	100x50 Rec Tube	AJ	26/08/2017	S	6	MF	3		150.47
1584	120x50 L Tube	SH	26/01/2016	S	6	MF	2.4	11.25	144.51
2025	120x50 L Tube	AJ	06/09/2017	S	6	MF	2.4		144.51
1132	150x50 Rec Tube	SH	17/11/2016	S	6	MF	2	10.35	131.46
1786	15x15 Eq Angle	SH	29/05/2017	S	6	MF	1.5	10.75	7.48
1711	15x15 Eq Angle	SH	11/04/2017	S	6	WH	1.5	10.5	7.3
1591	16mm Flat Bar	SH	26/01/2016	S	6	MF	3	11.25	8.77
1710	16mm Round Bar	SH	11/04/2017	S	6	WH	16	10.5	34.2
905	16mm Round Bar	SH	24/02/2017	S	6	MF	16	10.75	35.02	Just need this entry because its the maximum. The Above appears should not be in the results.
1660	16mm Round Pipe	AJ	24/03/2017	S	6	MF	3	10.5	20.85
1488	16x16 Sq Tube	SH	26/06/2016	S	6	MF	3	10.5	26.52
1794	20mm Round Pipe	SH	01/06/2017	S	6	MF	2	10.75	19.67
1411	20x10 Rec Tube	SH	23/08/2016	S	6	MF	1.5	10.4	27.2
1578	20x20 Sq Tube	SH	03/01/2016	S	6	MF	1.9	11.5	25.66
906	30mm Flat Bar	SH	24/02/2017	S	6	MF	5	10.75	26.18
1810	30x30 Eq Angle	SH	07/06/2017	S	6	MF	3	10.75	29.73
1768	30x30 Sq Tube	SH	14/05/2017	S	6	MF	2	10.75	39.02
1577	40x20 Rec Tube	SH	03/01/2016	S	6	MF	2	11.5	41.88
1809	40x40 Eq Angle	SH	07/06/2017	S	6	MF	3	10.75	40.18
1512	4cm U Channel	SH	21/07/2016	S	6	WH	20	11.5	16
1136	50mm Flat Bar	SH	17/11/2016	S	6	MF	5	10.35	41.91
1379	50mm Flat Bar	DH	10/08/2016	S	6	MF	3		25.51
1536	50mm Flat Bar	SH	06/06/2016	S	6	MF	3	10.5	25.51
1787	50x20 Rec Tube	SH	29/05/2017	S	6	MF	1.8	10.75	41.6
1587	50x20 UnEq Angle	SH	26/01/2016	S	6	MF	1.9	11.25	23.55
1407	50x20 UnEq Angle	SH	23/08/2016	S	6	MF	1.8	10.4	21.77
1588	50x50 Eq Angle	SH	26/01/2016	S	6	MF	5	11.25	86.53
1563	50x50 Sq Tube	SH	26/04/2016	S	6	MF	2.8	10.5	89.9
1445	50x50 Sq Tube	DH	06/06/2016	S	6	MF	2.8		89.9
1399	50x50 Sq Tube	DH	20/08/2016	S	6	MF	2.8		89.9
1576	50x50 Sq Tube	SH	03/01/2016	S	6	MF	2	11.5	71.55
1377	50x50 Sq Tube	SH	08/08/2016	S	6	MF	2.8	10.5	89.9
1690	50x50 Sq Tube	SH	08/04/2017	S	6	MF	2.8	10.5	89.9
1509	60mm Round Pipe	SH	10/07/2016	S	6	MF	3	10.5	91.41	60mm Diameter
1559	60x20 Rec Tube	SH	08/04/2016	S	6	MF	2	10.5	51.72
1558	60x60 Sq Tube	SH	05/04/2016	S	6	MF	2	10.5	78.93
1575	70x50 L Tube	SH	03/01/2016	S	6	MF	2.4	11.5	103.01
1597	80x80 Sq Tube	SH	10/02/2016	S	6	MF	1.6	11.25	104.08

June Here are a few problems with the query you designed. It doesnt list all the item names too. Itemsname are 166 but it gives only 43.