order column from union select by another column
I have a table:
Message
id, user_from_id, user_to_id, send_date, message, is_readed
and I would like to select all the users that I chat with, so my query looks like this:
SELECT user_from_id as user_id
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id
FROM message
WHERE user_from_id=xxx
..and I have a list of user_id's, the problem is that I can't find a way to order those id's by send_data, so that at the beginning of that list i would have a user that i lately chat with
Can someone help me?
mysql sql sql-order-by union
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
add a comment |
I have a table:
Message
id, user_from_id, user_to_id, send_date, message, is_readed
and I would like to select all the users that I chat with, so my query looks like this:
SELECT user_from_id as user_id
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id
FROM message
WHERE user_from_id=xxx
..and I have a list of user_id's, the problem is that I can't find a way to order those id's by send_data, so that at the beginning of that list i would have a user that i lately chat with
Can someone help me?
mysql sql sql-order-by union
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
add a comment |
I have a table:
Message
id, user_from_id, user_to_id, send_date, message, is_readed
and I would like to select all the users that I chat with, so my query looks like this:
SELECT user_from_id as user_id
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id
FROM message
WHERE user_from_id=xxx
..and I have a list of user_id's, the problem is that I can't find a way to order those id's by send_data, so that at the beginning of that list i would have a user that i lately chat with
Can someone help me?
mysql sql sql-order-by union
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
I have a table:
Message
id, user_from_id, user_to_id, send_date, message, is_readed
and I would like to select all the users that I chat with, so my query looks like this:
SELECT user_from_id as user_id
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id
FROM message
WHERE user_from_id=xxx
..and I have a list of user_id's, the problem is that I can't find a way to order those id's by send_data, so that at the beginning of that list i would have a user that i lately chat with
Can someone help me?
mysql sql sql-order-by union
mysql sql sql-order-by union
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
asked Nov 23 '18 at 13:21
Andrzej MAndrzej M
11
11
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
How about dispensing with the union?
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
ORDER BY m.send_date DESC;
This is probably not what you really want, because users will appear multiple times in the list. What you really want is:
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
GROUP BY m.user_id
ORDER BY MAX(m.send_date) DESC;
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
add a comment |
with x as(
SELECT user_from_id as user_id, send_date
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id, send_date
FROM message
WHERE user_from_id=xxx
) select user_id
from x
order by send_date
This wraps the UNION query up with the send_date column into a single variable, from which you can then SELECT and ORDER BY send_date.
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53447504%2forder-column-from-union-select-by-another-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
How about dispensing with the union?
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
ORDER BY m.send_date DESC;
This is probably not what you really want, because users will appear multiple times in the list. What you really want is:
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
GROUP BY m.user_id
ORDER BY MAX(m.send_date) DESC;
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
add a comment |
How about dispensing with the union?
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
ORDER BY m.send_date DESC;
This is probably not what you really want, because users will appear multiple times in the list. What you really want is:
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
GROUP BY m.user_id
ORDER BY MAX(m.send_date) DESC;
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
add a comment |
How about dispensing with the union?
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
ORDER BY m.send_date DESC;
This is probably not what you really want, because users will appear multiple times in the list. What you really want is:
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
GROUP BY m.user_id
ORDER BY MAX(m.send_date) DESC;
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
How about dispensing with the union?
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
ORDER BY m.send_date DESC;
This is probably not what you really want, because users will appear multiple times in the list. What you really want is:
SELECT (CASE WHEN m.user_to_id = xxx THEN m.user_from_id
ELSE m.user_to_id
END) as user_id
FROM message m
WHERE xxx IN (m.user_to_id, m.user_from_id)
GROUP BY m.user_id
ORDER BY MAX(m.send_date) DESC;
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
answered Nov 23 '18 at 13:22
Gordon LinoffGordon Linoff
793k36318421
793k36318421
add a comment |
add a comment |
with x as(
SELECT user_from_id as user_id, send_date
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id, send_date
FROM message
WHERE user_from_id=xxx
) select user_id
from x
order by send_date
This wraps the UNION query up with the send_date column into a single variable, from which you can then SELECT and ORDER BY send_date.
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
add a comment |
with x as(
SELECT user_from_id as user_id, send_date
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id, send_date
FROM message
WHERE user_from_id=xxx
) select user_id
from x
order by send_date
This wraps the UNION query up with the send_date column into a single variable, from which you can then SELECT and ORDER BY send_date.
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
add a comment |
with x as(
SELECT user_from_id as user_id, send_date
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id, send_date
FROM message
WHERE user_from_id=xxx
) select user_id
from x
order by send_date
This wraps the UNION query up with the send_date column into a single variable, from which you can then SELECT and ORDER BY send_date.
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
with x as(
SELECT user_from_id as user_id, send_date
FROM message
WHERE user_to_id=xxx
UNION
SELECT user_to_id, send_date
FROM message
WHERE user_from_id=xxx
) select user_id
from x
order by send_date
This wraps the UNION query up with the send_date column into a single variable, from which you can then SELECT and ORDER BY send_date.
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
edited Nov 23 '18 at 15:11
Benjamin Howarth
2,75131423
2,75131423
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
answered Nov 23 '18 at 13:26
picklerickpicklerick
32918
32918
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53447504%2forder-column-from-union-select-by-another-column%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
