CODEWITHSUNDEEP
sqlmariadb
Oliver Nybroe
Oliver Nybroe

Reputation: 1866

MariaDB - GROUP BY with an order

So I have a dataset, where I would like to order it based on strings ORDER BY FIELD(field_name, ...) after the order I wan't it to group the dataset based on another column.

I have tried with a subquery, but it seems like it ignores by ORDER BY when it gets subqueried.

This is the query I would like to group with GROUP BY setting_id

SELECT *
FROM `setting_values`
WHERE ((`owned_by_type` = 'App\\Models\\Utecca\\User' AND `owned_by_id` = 1 OR ((`owned_by_type` = 'App\\Models\\Utecca\\Agreement' AND `owned_by_id` = 1006))) OR (`owned_by_type` = 'App\\Models\\Utecca\\Employee' AND `owned_by_id` = 1)) AND `setting_values`.`deleted_at` IS NULL
ORDER BY FIELD(owned_by_type, 'App\\Models\\Utecca\\Employee', 'App\\Models\\Utecca\\Agreement', 'App\\Models\\Utecca\\User')

The order by works just fine, but I cannot get it to group it based on my order, it always selects the one with lowest primary key (id).

Here is my attempt which did not work.

SELECT * FROM (
    SELECT *
    FROM `setting_values`
    WHERE ((`owned_by_type` = 'App\\Models\\Utecca\\User' AND `owned_by_id` = 1 OR ((`owned_by_type` = 'App\\Models\\Utecca\\Agreement' AND `owned_by_id` = 1006))) OR (`owned_by_type` = 'App\\Models\\Utecca\\Employee' AND `owned_by_id` = 1)) AND `setting_values`.`deleted_at` IS NULL
    ORDER BY FIELD(owned_by_type, 'App\\Models\\Utecca\\Employee', 'App\\Models\\Utecca\\Agreement', 'App\\Models\\Utecca\\User')
) AS t
GROUP BY setting_id;

Here is some sample data enter image description here

What I am trying to accomplish with this sample data is 1 row with the id 3 as the row.
The desired result set from the query should obey these rules

Here is a SQLFiddle with it.

Running MariaDB version 10.2.6-MariaDB

Upvotes: 2

Views: 1294

Answers (1)

Rick James
Rick James

Reputation: 142268

First of all, the Optimizer is free to ignore the inner ORDER BY. So, please describe further what your intent is.

Getting past that, you can use a subquery:

SELECT ...
    FROM ( SELECT
            ...
            GROUP BY ...
            ORDER BY ...  -- This is lost unless followed by :
            LIMIT 9999999999  -- something valid; or very high (for all)
         ) AS x
    GROUP BY ...

Perhaps you are doing groupwise max ??

Upvotes: 2

Related Questions