0

Example table:

CREATE TABLE `level_2` (
  `id` int(11) NOT NULL,
  `comment_id` int(11) DEFAULT NULL,
  `comment_parent` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `user_photo` varchar(2083) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `url` varchar(2083) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `comment_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `comment_date` timestamp NULL DEFAULT NULL,
  `edited` tinyint(1) DEFAULT '0',
  `removed` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT;

Example rows:

INSERT INTO `level_2` (`id`, `comment_id`, `comment_parent`, `user_id`, `user_name`, `user_photo`, `url`, `comment_text`, `comment_date`, `edited`, `removed`) VALUES
(349, 407, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '1', '2019-03-05 00:07:41', 0, 0),
(350, 408, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '2', '2019-03-05 00:08:09', 0, 0),
(351, 412, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '3', '2019-03-05 00:55:23', 0, 0),
(352, 428, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '4', '2019-03-05 18:09:19', 0, 0),
(353, 430, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '555', '2019-03-05 18:28:37', 0, 0);

My query:

SELECT * 
FROM (SELECT DISTINCT comment_parent 
      FROM level_2 
      WHERE url = 'http://example.com' 
        AND removed = 0 LIMIT 10) cp, 
LATERAL (SELECT * 
         FROM level_2 
         WHERE url = 'http://example.com' 
           AND comment_parent = cp.comment_parent 
           AND removed = 0 
         ORDER BY comment_id DESC LIMIT 3) l2

Returns the comments with the id 353, 352, 351 sorted like that. But I need to return 351, 352, 353.

ORDER BY comment_id DESC in my query makes sure that I get 3 LAST comments with the same comment_parent and not 3 first comments with the same comment_parent. But also it returns 3 last comments in reverse order, while I want to return 3 last comments in natural order, not reversed.

I've also tried the following:

SELECT * 
FROM (SELECT DISTINCT comment_parent 
      FROM level_2 
      WHERE url = 'http://example.com' 
        AND removed = 0 
      LIMIT 10) cp, 
LATERAL (SELECT * 
         FROM (SELECT * 
               FROM level_2 
               WHERE url = 'http://example.com' 
                 AND comment_parent = cp.comment_parent 
                 AND removed = 0 
                 ORDER BY comment_id DESC LIMIT 3) sub 
         ORDER BY comment_id ASC) l2

but it outputs EXACTLY the same results, with the comments being ordered in DESC.

The following also gives the same undesired output:

SELECT * 
FROM (SELECT DISTINCT comment_parent 
      FROM level_2 
      WHERE url = 'http://example.com' 
        AND removed = 0 
      LIMIT 10) cp, 
LATERAL (SELECT * 
         FROM level_2 
         WHERE url = 'http://example.com' 
           AND comment_parent = cp.comment_parent 
           AND removed = 0 
         ORDER BY comment_id DESC LIMIT 3) l2, 
LATERAL (SELECT * 
         FROM level_2 
         WHERE id = l2.id 
         ORDER BY comment_id ASC) id

So no matter what I try, it still sorts that group of 3 comments in the desc order.

  • You need to specify the desired order in the outermost select. – mustaccio Mar 7 at 0:31
  • Please define what's the outermost select in my case. – COOLak Mar 7 at 0:33
  • If you mean the first part before LATERAL, it uses a disctinct select and can't use ORDER BY. – COOLak Mar 7 at 1:05
  • Any ordering set in a subquery may be (and in most cases will be) ignored in outer query. Any ordering without limitation set in subquery will be visually ignored unconditionally except the case when UFVs are used. – Akina Mar 7 at 5:41
  • Why not just tell me the right query then? – COOLak Mar 7 at 7:05

Your Answer

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

Browse other questions tagged or ask your own question.