Question 190753 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Order by unique column, but keep matching columns together

I’m trying to return a sorted list of entries by their date, but keep matching columns together.

For a super simplified example, the table food is as follows.

id name group_uuid date
1 Apple 6d37ca 2023-05-20
2 Cucumber d90ad2 2023-05-20
3 Orange 6d37ca 2023-06-14
4 Carrot d90ad2 2023-05-20
5 Walnut 03a7f3 2023-06-14
6 Pecan 03a7f3 2023-06-14

Is there a way to produce a result where the Orange comes directly after Apple, but not ordering by group_uuid alphabetically? I’d want to keep the group 6d37ca at the top since its first entry has an id of 1.

If I use SELECT * FROM food ORDER BY date ASC, type, id ASC it’ll produce the following result:

id name group_uuid date
1 Apple 6d37ca 2023-05-20
2 Cucumber d90ad2 2023-05-20
4 Carrot vegetable 2023-05-20
3 Orange d90ad2 2023-06-14
5 Walnut 03a7f3 2023-06-14
6 Pecan 03a7f3 2023-06-14

I’d ideally want the result to look like this:

id name group_uuid date
1 Apple 6d37ca 2023-05-20
3 Orange 6d37ca 2023-06-14
2 Cucumber d90ad2 2023-05-20
4 Carrot d90ad2 2023-05-20
5 Walnut 03a7f3 2023-06-14
6 Pecan 03a7f3 2023-06-14

Not sure if it’s possible or not, but any help would be appreciated.

Tags:

2

Answers


  1. 0

    You will need a subquery to get the group and minimum id of the group, and thus defined combine the group with the main query to then sort by the minemo id of the subquery.

    Edit: after 47 tests lol sqlfinddle o/

    Exemple:

    CREATE TABLE food (
    id INT,
    name VARCHAR(255),
    group_uuid VARCHAR(6),
    date DATE
);
INSERT INTO food (id, name, group_uuid, date)
VALUES
    (1, 'Apple', '6d37ca', '2023-05-20'),
    (2, 'Cucumber', 'd90ad2', '2023-05-20'),
    (3, 'Orange', '6d37ca', '2023-06-14'),
    (4, 'Carrot', 'd90ad2', '2023-05-20'),
    (5, 'Walnut', '03a7f3', '2023-06-14'),
    (6, 'Pecan', '03a7f3', '2023-06-14');

    And the resulting query:

    SELECT f.id, f.name, f.group_uuid, f.date
  FROM food f,
       (SELECT group_uuid, MIN(id) AS min_id
          FROM food 
         GROUP BY group_uuid) AS f2
 WHERE f.group_uuid = f2.group_uuid
 ORDER BY f2.min_id;

    Resulting:

    id name group_uuid date
    1 Apple 6d37ca 2023-05-20
    3 Orange 6d37ca 2023-06-14
    2 Cucumber d90ad2 2023-05-20
    4 Carrot d90ad2 2023-05-20
    5 Walnut 03a7f3 2023-06-14
    6 Pecan 03a7f3 2023-06-14
    Login or Signup to reply.
  2. 0

    OK full answer extracted from my previous comments:

    Just putting Apple in first position and Orange is second is straightforward enough. You use a CASE WHEN statement to order your elements according to your arbitrary desire (Apple is 0, Orange is 1, everything else is 2) then further refine the order for all other elements as needed:

    SELECT *
FROM food
ORDER BY CASE Name WHEN 'Apple' THEN 0 WHEN 'Orange' THEN 1 ELSE 2, date ASC, type, id ASC

    note that the ASC there are superfluous as they are the default in most cases.

    However you mentioned you ‘want to keep the group 6d37ca at the top’, and your desired result indicates you’re actually shooting for ordered groups, with each group’s position being decided by their member with the lowest ID. The first step is to get that lowest ID, which is done using the GROUP BY statement and the MIN() aggregate function:

    SELECT MIN(id) group_min_id, group_uuid
FROM food
GROUP BY group_uuid

    You then need to join this to your entire table, so each row in food "learns of" the lowest ID of the group they belong to:

    SELECT *
FROM food
INNER JOIN (
  SELECT MIN(id) group_min_id, group_uuid
  FROM food
  GROUP BY group_uuid) T1
ON food.group_uuid = T1.group_uuid

    T1 here is an ALIAS, that is necessary to identify the temporary table created by our sub-query. It also allows us to differenciate between the two group_uuid columns we have to handle during the JOIN (this can also be done by giving an alias to the group_uuid column!).

    Now, you don’t need to have group_uuid twice, and the group_min_id, while necessary to order your results, is not part of it either, so we’ll just select food.* (instead of the entire *), and add the ordering statement. Note that we do not order over group_uuid, since group_min_id already handles this part:

    SELECT food.*
FROM food
JOIN (
  SELECT MIN(id) group_min_id, group_uuid
  FROM food
  GROUP BY group_uuid) T1
ON food.group_uuid = T1.group_uuid
ORDER BY group_min_id, id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search