Reverse the order in the table derived from lateral joinUnexplained InnoDB timeouts“Lost connection to...
Correct physics behind the colors on CD (compact disc)?
Why would the IRS ask for birth certificates or even audit a small tax return?
Why do phishing e-mails use faked e-mail addresses instead of the real one?
What is the minimum amount of skill points per HD?
Can an earth elemental drown/bury its opponent underground using earth glide?
Reason why dimensional travelling would be restricted
Can a space-faring robot still function over a billion years?
School performs periodic password audits. Is my password compromised?
Specific Chinese carabiner QA?
Is there a way to find out the age of climbing ropes?
How to get the first element while continue streaming?
Four buttons on a table
Why is it "take a leak?"
Being asked to review a paper in conference one has submitted to
PTIJ: Aharon, King of Egypt
Can I cast a spell through the Invoke Duplicity clone while inside a Forcecage?
Rationale to prefer local variables over instance variables?
Is there a frame of reference in which I was born before I was conceived?
Why doesn't "adolescent" take any articles in "listen to adolescent agonising"?
A bug in Excel? Conditional formatting for marking duplicates also highlights unique value
Where is the fallacy here?
Is there a math equivalent to the conditional ternary operator?
Is there a limit on the maximum number of future jobs queued in an org?
Wardrobe above a wall with fuse boxes
Reverse the order in the table derived from lateral join
Unexplained InnoDB timeouts“Lost connection to MySQL server during query” errorHow to optimize indexes on MySQL query with various sortsIdentical query, tables, but different EXPLAIN and performanceMysql join not workingNeed help improving sql query performanceHow to improve query count execution with mySql replicate?select MAX() from MySQL view (2x INNER JOIN) is slowslow queries on indexed columns (large datasets)Subquery and subselection for Count/Number doesnt work
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.
mysql mysql-8.0
asked 4 mins ago
COOLakCOOLak
32
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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.
mysql mysql-8.0
asked 4 mins ago
COOLakCOOLak
32
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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.
mysql mysql-8.0
asked 4 mins ago
COOLakCOOLak
32
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.
mysql mysql-8.0
mysql mysql-8.0
asked 4 mins ago
COOLakCOOLak
32
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
COOLakCOOLak
32
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
COOLakCOOLak
32
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
COOLakCOOLak
32
asked 4 mins ago
COOLakCOOLak
32
32
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
COOLak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
COOLak is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231496%2freverse-the-order-in-the-table-derived-from-lateral-join%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
COOLak is a new contributor. Be nice, and check out our Code of Conduct.
COOLak is a new contributor. Be nice, and check out our Code of Conduct.
COOLak is a new contributor. Be nice, and check out our Code of Conduct.
COOLak is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231496%2freverse-the-order-in-the-table-derived-from-lateral-join%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
