I am clearly misundersatanding something here with MySQL's GROUP BY as it is changing the order of my results.
Using this example SQL data:
CREATE TABLE IF NOT EXISTS `example_table` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `GROUP_NAME` text NOT NULL, `ORDER_COLUMN` int(11) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; INSERT INTO `example_table` (`ID`, `GROUP_NAME`, `ORDER_COLUMN`) VALUES (NULL, '271007K240.003:10', 70), (NULL, '271007K240.003:10', 90), (NULL, '271007K240.003:10', 100), (NULL, '271007K240.003:10', 50), (NULL, '271007K240.003:10', 80), (NULL, '271007K240.003:10', 60);
Now I've created this data as an example so there would be lots of different values in the GROUP_NAME column., but for clairty I've just included what demonstrates the issue.
Running this very simple query, returns the highest ORDER_COLUMN (ID: 3) at the top as expected:
SELECT * FROM `example_table` ORDER BY ORDER_COLUMN DESC
However I've actually wanting to group by the column I've named here GROUP_NAME, sowith this in mind I was anting to do asomething like so:
SELECT * FROM `example_table` GROUP BY GROUP_NAME ORDER BY ORDER_COLUMN DESC
Now doing this just simply returns the first row, and ignores the ORDER BY.
I then thought to achieve what I need, I would need to use a sub-query with the data pre-sorted and then the group by can just group the sub-queries data-set like so:
SELECT * FROM ( SELECT * FROM `example_table` ORDER BY ORDER_COLUMN DESC ) AS TEMP_TABLE GROUP BY GROUP_NAME
Unfortunately though, this still returns just the first row of the table. What am I doing wrong here?