TechQA.

MySQL/Mariadb problem: `Order by DESC` before `Group by`

564 views Asked by At

Summary: I want to Order by before Group

I find a nice article about the problem, but finally not successful to fix it. https://eddies-shop.medium.com/mysql-when-to-order-before-group-13d54d6c4ebb

My Server config:

  • Server type: MariaDB
  • Server version: 10.6.4-MariaDB - Arch Linux

About the query: I get a list of rooms and messages, but I only need the latest message for each room. so I need to group by conversation_id, and sort by message_id or message_time.

The above query works well but is not complete. In that, for each room, we have duplicate rows.

When I try and uncomment the last line from the query And when I try to apply GROUP BY main.conversation_id. It is no longer in order and the order is broken again.

My Query:

SELECT
  main.*
FROM
(
    SELECT
        sub.*
    FROM
    (
        SELECT
        conversation.id AS conversation_id,
        CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
        conversation.is_group AS conversation_isgroup,
        (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
    
        message.id AS message_id,
        message.type AS message_type,
        message.body AS message_body,
        message.filename AS message_filename,
        message.created_at AS message_time,

        message.user_id AS message_user_id,
        CONCAT(user.first_name, " ", user.last_name) AS message_user_name
    FROM
        conversation
    INNER JOIN
        conversation_member
        ON
            conversation_member.conversation_id = conversation.id
    LEFT JOIN
        message
        ON
            message.conversation_id = conversation.id
    LEFT JOIN
        user
        ON
            user.id = message.user_id
    LEFT JOIN
        user as user2
        ON
            user2.id = conversation.owner2_id
            OR
            user2.id = conversation.owner_id
    WHERE
        user2.id != 1
        AND
        name IS NULL
        AND
        conversation_member.user_id = 1
        AND
        conversation.is_group = 0
    ) AS sub
    ORDER BY
      sub.message_id DESC
) as main
# GROUP BY
# main.conversation_id

If you need to know more about the Database structure:

--
-- Table structure for table `conversation`
--

CREATE TABLE `conversation` (
  `id` int(50) NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `owner_id` int(50) NOT NULL,
  `owner2_id` int(50) DEFAULT NULL,
  `is_group` int(2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `conversation`
--

INSERT INTO `conversation` (`id`, `name`, `owner_id`, `owner2_id`, `is_group`, `created_at`, `updated_at`) VALUES
(7, 'تالار گفتگوی ریاضی', 1, NULL, 1, '2021-09-13 20:33:38', NULL),
(8, NULL, 2, 1, 0, '2021-09-13 20:33:46', '2021-09-14 07:55:44'),
(9, 'گروه ازمایشی', 3, NULL, 1, '2021-09-14 07:45:04', NULL),
(10, 'پروژه ها و ایده ها', 3, NULL, 1, '2021-09-14 07:47:19', NULL),
(11, NULL, 4, 1, 0, '2021-09-14 08:05:11', NULL);

--
-- Table structure for table `conversation_member`
--

CREATE TABLE `conversation_member` (
  `id` int(50) NOT NULL,
  `conversation_id` int(50) NOT NULL,
  `user_id` int(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `conversation_member`
--

INSERT INTO `conversation_member` (`id`, `conversation_id`, `user_id`, `created_at`) VALUES
(1, 8, 1, '2021-09-14 07:02:18'),
(2, 8, 2, '2021-09-14 07:02:18'),
(3, 7, 1, '2021-09-14 07:02:28'),
(4, 7, 3, '2021-09-14 07:02:28'),
(5, 9, 3, '2021-09-14 07:45:13'),
(7, 10, 4, '2021-09-14 08:02:57'),
(8, 10, 2, '2021-09-14 08:02:57'),
(9, 10, 1, '2021-09-14 08:03:05'),
(10, 11, 4, '2021-09-14 08:05:23'),
(11, 11, 1, '2021-09-14 08:05:23'),
(12, 7, 4, '2021-09-14 09:30:04');

--
-- Table structure for table `message`
--

CREATE TABLE `message` (
  `id` int(50) NOT NULL,
  `conversation_id` int(50) NOT NULL,
  `user_id` int(50) DEFAULT NULL,
  `type` int(2) NOT NULL COMMENT '0=system,1=message,1=file,2=voice',
  `body` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `filename` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_group` int(2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `edited_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `message`
--

INSERT INTO `message` (`id`, `conversation_id`, `user_id`, `type`, `body`, `filename`, `is_group`, `created_at`, `edited_at`) VALUES
(1, 7, 1, 1, '1', NULL, 1, '2021-09-14 07:16:12', '2021-09-14 14:14:44'),
(2, 8, 1, 1, '2', NULL, 0, '2021-09-14 07:16:25', '2021-09-14 14:14:45'),
(3, 11, 1, 1, '3', NULL, 0, '2021-09-14 13:21:30', '2021-09-14 14:14:47'),
(4, 10, 1, 1, '4', NULL, 1, '2021-09-14 13:23:34', '2021-09-14 14:14:49'),
(5, 7, 1, 1, '5', NULL, 1, '2021-09-14 13:25:16', '2021-09-14 14:14:51'),
(6, 7, 1, 1, '6', NULL, 1, '2021-09-14 13:30:40', '2021-09-14 14:14:52'),
(7, 7, 1, 1, '7', NULL, 1, '2021-09-14 13:49:29', '2021-09-14 14:14:54'),
(8, 7, 1, 1, '8', NULL, 1, '2021-09-14 13:49:34', '2021-09-14 14:14:56'),
(9, 10, 1, 1, '9', NULL, 1, '2021-09-14 13:54:04', '2021-09-14 14:14:57'),
(10, 7, 1, 1, '10', NULL, 1, '2021-09-14 14:01:18', '2021-09-14 14:14:59'),
(11, 8, 1, 1, '11', NULL, 0, '2021-09-14 14:07:48', '2021-09-14 14:15:03'),
(12, 11, 4, 1, 'test-new', NULL, 0, '2021-09-14 15:11:51', NULL),
(13, 11, 1, 1, 'fdgdfg', NULL, 0, '2021-09-14 13:21:30', '2021-09-14 14:14:47'),
(14, 11, 1, 1, 'sd1f23sd1f', NULL, 0, '2021-09-14 15:33:02', NULL),
(15, 11, 1, 1, 'dfgdfgdfgdfg', NULL, 0, '2021-09-14 15:33:02', NULL),
(16, 11, 1, 1, 'dfgdfgdfg', NULL, 0, '2021-09-14 15:33:06', NULL),
(17, 11, 1, 1, 'dfg345345345', NULL, 0, '2021-09-14 15:33:06', NULL),
(18, 11, 1, 1, 'gdfg234234234', NULL, 0, '2021-09-14 15:33:17', NULL),
(19, 11, 1, 1, 'dfgda1323123f', NULL, 0, '2021-09-14 15:33:17', NULL),
(20, 11, 1, 1, '234234234234', NULL, 0, '2021-09-14 15:33:17', NULL),
(21, 11, 1, 1, '345345345345', NULL, 0, '2021-09-14 15:33:17', NULL),
(22, 11, 1, 1, '5565656', NULL, 0, '2021-09-14 15:33:17', NULL),
(23, 11, 1, 1, '7787878', NULL, 0, '2021-09-14 15:33:17', NULL),
(24, 11, 1, 1, 'یبلیبلیبلیبل', NULL, 0, '2021-09-14 15:33:28', NULL),
(25, 11, 1, 1, 'ض۳۲ث۱۲۳۴۲۳۴۲۳۴', NULL, 0, '2021-09-14 15:33:28', NULL),
(26, 11, 1, 1, '۳۴۵سیبیلبیبلب', NULL, 0, '2021-09-14 15:33:28', NULL),
(27, 11, 1, 1, 'فقفثفثقفثقف', NULL, 0, '2021-09-14 15:33:28', NULL),
(28, 11, 1, 1, '۳۳۴۲۴۲۳۴', NULL, 0, '2021-09-14 15:33:28', NULL),
(29, 11, 1, 1, '$$$$', NULL, 0, '2021-09-14 15:33:28', NULL),
(30, 11, 1, 1, '$$$%%dfgdfg', NULL, 0, '2021-09-14 15:33:47', NULL),
(31, 11, 1, 1, 'dfgdfg23423423423سیبلیبل', NULL, 0, '2021-09-14 15:33:47', NULL),
(32, 11, 1, 1, 'یبلص۴۳۵۲۳۴۵۳۴۵۳۴۵', NULL, 0, '2021-09-14 15:33:47', NULL),
(33, 11, 1, 1, 'یبل۳۵۳۴۵فثقیبلیبلیبل', NULL, 0, '2021-09-14 15:33:47', NULL),
(34, 11, 1, 1, 'یبلیلبل۳۴۵۳۴۵۳۴۵۳۴۵۳۴۵', NULL, 0, '2021-09-14 15:33:47', NULL),
(35, 11, 1, 1, '$$$$$####', NULL, 0, '2021-09-14 15:33:47', NULL);

--
-- Table structure for table `message_view`
--

CREATE TABLE `message_view` (
  `id` int(50) NOT NULL,
  `message_id` int(50) NOT NULL,
  `conversation_id` int(50) NOT NULL,
  `is_group` int(2) NOT NULL,
  `user_id` int(50) NOT NULL,
  `viewed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `session`
--

CREATE TABLE `session` (
  `id` int(50) NOT NULL,
  `user_id` int(50) NOT NULL,
  `device` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` int(10) DEFAULT NULL,
  `secret` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `session`
--

INSERT INTO `session` (`id`, `user_id`, `device`, `code`, `secret`) VALUES
(1, 1, '08ad5559-15f7-4c32-ab2d-2d8a563670a3', 0, '1'),
(2, 1, 'c8216675-08ff-4deb-9341-2afbe88bc716', NULL, '41234'),
(3, 1, 'f20279f4-615d-4b6f-83e4-0e6c201395e3', 53741, NULL),
(4, 1, 'bef6f3e2-04b7-4ff5-b948-c035c376f4d2', 51003, NULL),
(5, 1, '3492d860-6ae4-4a00-a65d-346d880c4e71', 30646, NULL),
(6, 1, 'fd0b2d35-d7aa-4e40-b362-6b592dc17aad', 28576, NULL),
(7, 1, 'c69516b4-53a8-4e69-a874-a11d859b451d', 32440, NULL),
(8, 1, '075149a4-d94c-4246-99ce-d0b71c72f26c', 99800, NULL),
(9, 1, '4804b854-89b6-4c85-8df3-815ffdd34fba', 19774, NULL),
(10, 1, 'a60195ac-2e50-42ed-9d71-1d3d04729339', 10262, NULL),
(11, 1, '80a9a2e7-ee8b-47ef-8ca4-75216721a6ac', 88424, 'bc82131e-0e59-4841-98b7-798cf65d9fcb'),
(12, 1, '6c120179-312f-4d73-9488-7f692cb54234', 42832, NULL),
(13, 1, 'a8b7ca5b-a47d-48b0-afeb-197b5ec7dc44', 39034, 'fdd51fe1-5bae-424f-9515-1a1b435faed6'),
(14, 1, '0652aa2a-01df-497d-b7b2-77fc87a29c24', 36133, '8fc206e1-1a49-444b-bcb6-ea289c17a918'),
(15, 1, '6b0bf6b9-4d32-4c6b-9e84-fd0b393acb31', 87972, '05de4b7a-cc6e-4ff0-b321-72473ac903bd'),
(16, 1, '0a523464-89ab-4f4a-803e-ca252e637e4f', 40843, 'a35a2169-949d-4043-a061-1a56ec30440e'),
(17, 1, '34b2e1bf-a088-48e8-85b8-1943db001fd5', 65916, '971a3cea-8ccc-45bf-887e-7797e4c6ab22'),
(18, 1, 'fcdb6c98-d044-43e8-a373-351f7ca1536d', 82257, 'bacb442d-e066-4117-a380-c468316d47f2'),
(19, 1, '565a036d-c4f8-46b0-8493-e5371e3dccca', 71626, '610b4e81-cf72-4091-a711-d64c601e0f0c'),
(20, 1, '8775a2d9-4544-48c4-ab5c-6d7216c955f7', 46912, 'b5eabb83-e372-4e7b-a48a-3c99eaba5d6d'),
(21, 1, 'a94f734b-0aa0-4ee9-aea3-86e2a405cf56', 44508, '2db51630-e32a-4172-90aa-99d5f7b00063'),
(22, 1, 'b0ed316b-483b-47cc-b27f-2fe6b83f410a', 87850, 'f68377a4-3ed8-4c36-8a78-1d807ed50449'),
(23, 1, '08f38458-cd12-4a9c-9c63-9c6aa291956b', 79266, 'e6314070-8401-4d7b-b9d7-a44c147c75ec'),
(24, 4, '192dfd3f-b2d7-401f-bf95-2d663b6badab', 65088, '6fa66aa9-f47d-46fd-9ce5-9645802383da'),
(25, 4, 'fc8fe94b-f220-42db-ab77-74de994f8275', 37715, '0e188502-67c2-44a9-b68c-32b07fa150ab'),
(26, 1, '27451de6-c730-450a-b76c-3ea53ff74580', 16934, '662ae741-fa42-4fc3-b0bb-5ae6c9e67b52'),
(27, 4, '6ba92f55-cffe-4d9e-b646-be9cf07e99e3', 91683, '27b85d09-831d-41cd-8032-17743a76616d');

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` int(50) NOT NULL,
  `first_name` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_code` int(2) NOT NULL,
  `phone_number` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `first_name`, `last_name`, `country_code`, `phone_number`, `created_at`, `updated_at`) VALUES
(1, 'Max', 'Base', 98, '9134458080', '2021-09-13 18:28:47', '2021-09-14 07:13:21'),
(2, 'Ali', 'Tahmasebi', 98, '91032545254', '2021-09-14 07:02:46', '2021-09-14 08:02:24'),
(3, 'B.', 'KheirKhah', 98, '9124554020', '2021-09-14 07:02:46', '2021-09-14 08:03:20'),
(4, 'H.', 'Malekian', 98, '9134550773', '2021-09-14 07:25:21', '2021-09-14 08:02:35');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `conversation`
--
ALTER TABLE `conversation`
  ADD PRIMARY KEY (`id`),
  ADD KEY `owner_id` (`owner_id`),
  ADD KEY `is_group` (`is_group`);

--
-- Indexes for table `conversation_member`
--
ALTER TABLE `conversation_member`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `message`
--
ALTER TABLE `message`
  ADD PRIMARY KEY (`id`),
  ADD KEY `is_group` (`is_group`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `conversation_id` (`conversation_id`);

--
-- Indexes for table `message_view`
--
ALTER TABLE `message_view`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `session`
--
ALTER TABLE `session`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `device` (`device`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD KEY `phone_number` (`phone_number`),
  ADD KEY `country_code` (`country_code`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `conversation`
--
ALTER TABLE `conversation`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `conversation_member`
--
ALTER TABLE `conversation_member`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `message`
--
ALTER TABLE `message`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `message_view`
--
ALTER TABLE `message_view`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `session`
--
ALTER TABLE `session`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

A bit more about the database and tables:

Purpose of conversation, conversation_member table: I have a database to store messenger data. there are 2 model conversations:

  • 1: Group: multi people
  • 2: Personal chat: a user to another user (It's why I have owner2_id column.)

And the main query is this to merge personal chat and group chats of user who ID = 1:

(
    SELECT
        sub1.*
    FROM
    (
        SELECT
            conversation.id AS conversation_id,
            conversation.name AS conversation_name,
            conversation.is_group AS conversation_isgroup,
            conversation.owner_id AS conversation_owner_id,

            message.id AS message_id,
            message.type AS message_type,
            message.body AS message_body,
            message.filename AS message_filename,
            message.created_at AS message_time,

            message.user_id AS message_user_id,
            CONCAT(user.first_name, " ", user.last_name) AS message_user_name
        FROM
            conversation
        INNER JOIN
            conversation_member
            ON
                conversation_member.conversation_id = conversation.id
        LEFT JOIN
            message
            ON
                message.conversation_id = conversation.id
        LEFT JOIN
            user
            ON
                user.id = message.user_id
        WHERE
            conversation_member.user_id = 1
            AND
            conversation.is_group = 1
    ) AS sub1
    GROUP BY sub1.message_id desc
)
UNION
(
    SELECT
        sub2.*
    FROM
    (
        SELECT
            conversation.id AS conversation_id,
            CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
            conversation.is_group AS conversation_isgroup,
            (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,

            message.id AS message_id,
            message.type AS message_type,
            message.body AS message_body,
            message.filename AS message_filename,
            message.created_at AS message_time,

            message.user_id AS message_user_id,
            CONCAT(user.first_name, " ", user.last_name) AS message_user_name
        FROM
            conversation
        INNER JOIN
            conversation_member
            ON
                conversation_member.conversation_id = conversation.id
        LEFT JOIN
            message
            ON
                message.conversation_id = conversation.id
        LEFT JOIN
            user
            ON
                user.id = message.user_id
        LEFT JOIN
            user as user2
            ON
                user2.id = conversation.owner2_id
                OR
                user2.id = conversation.owner_id
        WHERE
            user2.id != 1
            AND
            name IS NULL
            AND
            conversation_member.user_id = 1
            AND
            conversation.is_group = 0
    ) AS sub2
    GROUP BY sub2.message_id desc
);

WHAT I'M DOING

I want a list of all group conversations and personal conversations and the last messages in that room and sort all of the rooms by last MESSAGE_TIME.

This is what happens to almost all messengers.

mysql sql mariadb mariadb-10.6
Original Q&A
2

There are 2 answers

2
Max Base On BEST ANSWER

I am not a master in SQL, by the way, I just success to fix the problem of one query after hours!

If you have an any better queries for this purpose, please post and answer.

This is only for one query, I have two queries and need to UNION and merge.

A single query:

SELECT
    main.*
FROM
(
    SELECT
        sub.*
    FROM
    (
        SELECT
        conversation.id AS conversation_id,
        CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
        conversation.is_group AS conversation_isgroup,
        (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
        
        message.id AS message_id,
        message.type AS message_type,
        message.body AS message_body,
        message.filename AS message_filename,
        message.created_at AS message_time,

        message.user_id AS message_user_id,
        CONCAT(user.first_name, " ", user.last_name) AS message_user_name
    FROM
        conversation
    INNER JOIN
        conversation_member
        ON
            conversation_member.conversation_id = conversation.id
    LEFT JOIN
        message
        ON
            message.conversation_id = conversation.id
    LEFT JOIN
        user
        ON
            user.id = message.user_id
    LEFT JOIN
        user as user2
        ON
            user2.id = conversation.owner2_id
            OR
            user2.id = conversation.owner_id
    WHERE
        user2.id != 1
        AND
        name IS NULL
        AND
        conversation_member.user_id = 1
        AND
        conversation.is_group = 0
    ) AS sub
    GROUP BY sub.message_id desc
) as main
GROUP BY main.conversation_id

Main query and UNION to merge the results:

(
    SELECT
        mm.*
    FROM
    (
        (
            SELECT
                sub1.*
            FROM
            (
                SELECT
                    conversation.id AS conversation_id,
                    conversation.name AS conversation_name,
                    conversation.is_group AS conversation_isgroup,
                    conversation.owner_id AS conversation_owner_id,

                    message.id AS message_id,
                    message.type AS message_type,
                    message.body AS message_body,
                    message.filename AS message_filename,
                    message.created_at AS message_time,

                    message.user_id AS message_user_id,
                    CONCAT(user.first_name, " ", user.last_name) AS message_user_name
                FROM
                    conversation
                INNER JOIN
                    conversation_member
                    ON
                        conversation_member.conversation_id = conversation.id
                LEFT JOIN
                    message
                    ON
                        message.conversation_id = conversation.id
                LEFT JOIN
                    user
                    ON
                        user.id = message.user_id
                WHERE
                    conversation_member.user_id = 1
                    AND
                    conversation.is_group = 1
            ) AS sub1
            GROUP BY sub1.message_id desc
        )
        UNION
        (
            SELECT
                sub2.*
            FROM
            (
                SELECT
                    conversation.id AS conversation_id,
                    CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
                    conversation.is_group AS conversation_isgroup,
                    (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,

                    message.id AS message_id,
                    message.type AS message_type,
                    message.body AS message_body,
                    message.filename AS message_filename,
                    message.created_at AS message_time,

                    message.user_id AS message_user_id,
                    CONCAT(user.first_name, " ", user.last_name) AS message_user_name
                FROM
                    conversation
                INNER JOIN
                    conversation_member
                    ON
                        conversation_member.conversation_id = conversation.id
                LEFT JOIN
                    message
                    ON
                        message.conversation_id = conversation.id
                LEFT JOIN
                    user
                    ON
                        user.id = message.user_id
                LEFT JOIN
                    user as user2
                    ON
                        user2.id = conversation.owner2_id
                        OR
                        user2.id = conversation.owner_id
                WHERE
                    user2.id != 1
                    AND
                    name IS NULL
                    AND
                    conversation_member.user_id = 1
                    AND
                    conversation.is_group = 0
            ) AS sub2
            GROUP BY sub2.message_id desc
        )
    ) AS mm
    GROUP BY mm.conversation_id desc
)
;

I am not sure the query is optimized or good. but this works. please review and comment on my query if this is not good enough.

5
Ergest Basha On

I will only use the query on your question to show how MariaDB deal with the order by in subquery.

I changed your user table with user01 because I had a table user in my MariaDB database.

SELECT
  main.*
FROM
(
    SELECT
        sub.*
    FROM
    (
        SELECT
        conversation.id AS conversation_id,
        CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
        conversation.is_group AS conversation_isgroup,
        (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
    
        message.id AS message_id,
        message.type AS message_type,
        message.body AS message_body,
        message.filename AS message_filename,
        message.created_at AS message_time,

        message.user_id AS message_user_id,
        CONCAT(user01.first_name, " ", user01.last_name) AS message_user_name
    FROM
        conversation
    INNER JOIN
        conversation_member
        ON
            conversation_member.conversation_id = conversation.id
    LEFT JOIN
        message
        ON
            message.conversation_id = conversation.id
    LEFT JOIN
        user01
        ON
            user01.id = message.user_id
    LEFT JOIN
        user01 as user2
        ON
            user2.id = conversation.owner2_id
            OR
            user2.id = conversation.owner_id
    WHERE
        user2.id != 1
        AND
        name IS NULL
        AND
        conversation_member.user_id = 1
        AND
        conversation.is_group = 0
    ) AS sub
    ORDER BY
      sub.message_id DESC limit 10 
) as main
 GROUP BY
 main.conversation_id;

I only added limit 10 on sub.message_id DESC limit 10 .

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).

You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT

https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

enter image description here

Related Questions in MYSQL

Related Questions in SQL

Related Questions in MARIADB

Related Questions in MARIADB-10.6

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Trending Questions