CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
David Rodrigues
David Rodrigues

Reputation: 12532

GROUP BY after ORDER BY

I need to do GROUP BY after ORDER BY. I don't understand why MySQL doesn't support that. This is my code:

SELECT
    `pages`.`id`,
    `contents`.`id_language`,
    [...]

[...]

ORDER BY
    FIND_IN_SET(`languages`.`id`, '3') DESC

[the GROUP BY]

The results will be something like this:

id | id_language | ...
1    3
1    1
2    3
2    5
2    1

I need to group by ID, I need only the first result and I need to save in a view. I can't use a SUBQUERY because of that.

The result need to be:

id | id_language | ...
1    3
2    3

Note: Don't get confused by id_language = 3, because it isn't a rule.

Upvotes: 10

Views: 22857

Answers (4)

BitMaese
BitMaese

Reputation: 99

SELECT id, idl
FROM (SELECT
    `pages`.`id` as id,
    `contents`.`id_language` as idl,
    [...]

[...]

ORDER BY
    FIND_IN_SET(`languages`.`id`, '3') DESC
     ) d
GROUP BY d.id

Upvotes: 9

ajreal
ajreal

Reputation: 47311

Very amusing, try

select * from your_table
where id_language=3
order by id;

As far I can tell, the rule set is id_language=3,
which make no differences from using where

Upvotes: 0

Robert Martin
Robert Martin

Reputation: 17157

You may want an additional column in your original query that you GROUP BY, along with whatever you're currently grouping by. That column, when grouped, could then be used to order afterward. For instance:

SELECT
    SUM(IF(`languages`.`id` = 3, 1, 0)) AS languageOrder,
    `pages`.`id`,
    `contents`.`id_language`,
    [...]

[...]

[GROUP BY...]

ORDER BY languageOrder DESC

I would intend for languageOrder to be positive for groups that contain language #3, 0 otherwise. So groups that contain language 3 will be at the top.

Upvotes: 1

Jonathan Weatherhead
Jonathan Weatherhead

Reputation: 1580

Group By will group result sets, and is generally used for aggregation. Order By is the way that results are sorted.

Upvotes: 1

Related Questions