Reverse the order in the table derived from lateral join
PTIJ: Aharon, King of Egypt
Is divide-by-zero a security vulnerability?
Is every open circuit a capacitor?
Meaning of word ягоза
How to mitigate "bandwagon attacking" from players?
Would the melodic leap of the opening phrase of Mozart's K545 be considered dissonant?
What does each site of a vanilla 9.1 installation do?
Why is it "take a leak?"
Is there a frame of reference in which I was born before I was conceived?
How to get the first element while continue streaming?
Why is my Contribution Detail Report (native CiviCRM Core report) not accurate?
The need of reserving one's ability in job interviews
What is the meaning of "notice to quit at once" and "Lotty points”
Formatting a table to look nice
Can we carry rice to Japan?
How does insurance birth control work?
Deal the cards to the players
How can neutral atoms have exactly zero electric field when there is a difference in the positions of the charges?
Why did the Cray-1 have 8 parity bits per word?
What is the minimum amount of skill points per HD?
Being asked to review a paper in conference one has submitted to
Practical reasons to have both a large police force and bounty hunting network?
Lock enemy's y-axis when using Vector3.MoveTowards to follow the player
Do AL rules let me pick different starting equipment?
Reverse the order in the table derived from lateral join
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 1 min 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 1 min 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 1 min 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 1 min 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 1 min 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 1 min 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 1 min ago
COOLakCOOLak
32
asked 1 min 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
