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.