I have an issue with an SQL Query. Lets take this example data
itemID catID attrib1 attrib2
1 1 10 5
2 1 10 7
3 1 5 10
4 2 18 15
I want to return the best item for each category (with attrib1 having priority over attrib2)
Obviously, SELECT catID, MAX(attrib1), MAX(attrib2) FROM test_table GROUP BY catID
doesn't work since it will return 10 & 10 for the 1st cat.
So is there anyway to tell MySQL to select max value from attrib2 row but only consider the ones where attrib1 is also max value ? i.e return the following data
catID attrib1 attrib2
1 10 7
2 18 15
If you want to understand VALUE try this query which creates a virtual 1 column table: SELECT * FROM (VALUES (1), (5), (1)) as listOfValues(columnName) And this query which creates a virtual 2 column table: SELECT * FROM (VALUES (1,2), (5,3), (1,4)) as tableOfValues(columnName1, ColumnName2) Now you can understand why ...
We can group the resultset in SQL on multiple column values. When we define the grouping criteria on more than one column, all the records having the same value for the columns defined in the group by clause are collectively represented using a single record in the query output.
Yes, it is possible to use MySQL GROUP BY clause with multiple columns just as we can use MySQL DISTINCT clause.
Example - Using SQL GROUP BY ClauseIn some cases, you will be required to use the SQL GROUP BY clause with the SQL MAX function. For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department.
You can get the best attrib1 values, and then join in the attrib2 values and get the best of those for each attrib1 value:
select t2.catID, t2.attrib1, max(t2.attrib2)
from
(
select catID, max(attrib1) as attrib1
from test_table
group by catID
) t1
inner join test_table t2 on t2.catID = t1.catID and t2.attrib1 = t1.attrib1
group by t2.catID, t2.attrib1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With