0

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?

1

With this query:

select group_name, max(order_column) order_column
from example_table
group by group_name

you can get the max value of order_column for each id.
Then join it to the table:

select t.* 
from example_table t inner join ( 
  select group_name, max(order_column) order_column
  from example_table
  group by group_name
) g on g.group_name = t.group_name and g.order_column = t.order_column

See the demo.
Results:

| ID  | GROUP_NAME        | ORDER_COLUMN |
| --- | ----------------- | ------------ |
| 3   | 271007K240.003:10 | 100          |
0

Not completely sure what you want to accomplish, but did you try:

ORDER BY
    GROUP_NAME,
    ORDER_COLUMN

If that doesn't do the trick, how about adding more data with different group names and an example of what you expect the output to look like

  • Thanks for the reply. By adding this example shown above I.E groupingo n the field I was I received an error due to it not being aggregated data. Now, when grouping, the end game is to seata set here: db-fiddle.com/f/poNVD1jUkdL4eNqfPqVgQW/0 So, in this example, I have added two grouBy using the GROUP BY I was hoping to be able to retrieve only 2 rows from the database, one for each of the groups. Ths I want to retrieve are the rows with the highest ORDER_COLUMN values, hence me trying the GROUP BY and ORDER By together. – KingRidgehead Apr 14 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.