How to select distinct rows when using joint table
I have two tables named chatMaster and chatMessages , i was trying to left join the two tables to get all data from master and latest message for each chat. I am using this query to join the two tables
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn FROM chatMaster c
left join chatMessages m on c.id = m.id group by m.id ;
This query is for selecting only one row for each entry in chatMaster, but it is selecting the first message for every entry like,
1, 2018-11-24 00:40:08, HI!, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001
2, 2018-11-24 00:40:21, HI! There, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1
3, 2018-11-24 01:33:12, HI!, e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2
this is the result of select * from chatMessages, There are two entries for chat id 99e22056-ee7f-11e8-bc28-8acac6f59ef9 and i want the send one which is send on 2018-11-24 00:40:21 but the result of my join query is
99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001, 1, 1001, HI!, 2018-11-24 00:40:08
e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2, 10001, 2, HI!, 2018-11-24 01:33:12
It is selecting the first message. How to select the latest message? What changes should i make to get the latest message from chatMessages instead of first message. please ask if you need more details
mysql
asked Nov 23 '18 at 20:16
SonySony
3,86332639
|
show 1 more comment
I have two tables named chatMaster and chatMessages , i was trying to left join the two tables to get all data from master and latest message for each chat. I am using this query to join the two tables
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn FROM chatMaster c
left join chatMessages m on c.id = m.id group by m.id ;
This query is for selecting only one row for each entry in chatMaster, but it is selecting the first message for every entry like,
1, 2018-11-24 00:40:08, HI!, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001
2, 2018-11-24 00:40:21, HI! There, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1
3, 2018-11-24 01:33:12, HI!, e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2
this is the result of select * from chatMessages, There are two entries for chat id 99e22056-ee7f-11e8-bc28-8acac6f59ef9 and i want the send one which is send on 2018-11-24 00:40:21 but the result of my join query is
99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001, 1, 1001, HI!, 2018-11-24 00:40:08
e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2, 10001, 2, HI!, 2018-11-24 01:33:12
It is selecting the first message. How to select the latest message? What changes should i make to get the latest message from chatMessages instead of first message. please ask if you need more details
mysql
asked Nov 23 '18 at 20:16
SonySony
3,86332639
Why are you left joining ? I doubt a chatmaster can exist without any chatmessages in it ?
– Madhur Bhaiya
Nov 23 '18 at 20:18
1
Possible duplicate of MySQL: Get latest message from 2 tables that are associated with eachother
– Madhur Bhaiya
Nov 23 '18 at 20:19
Because when a chat request is accepted an entry is made in master table and at that time there will not be any entry in chatMessages
– Sony
Nov 23 '18 at 20:19
Check these answers: stackoverflow.com/a/53391638/2469308 and stackoverflow.com/a/53090014/2469308
– Madhur Bhaiya
Nov 23 '18 at 20:22
What is your MySQL server version ? Do you have access to the version 8.0+ ? Solution complexity will be significantly less in newer versions (with Window functions).
– Madhur Bhaiya
Nov 23 '18 at 20:24
|
show 1 more comment
I have two tables named chatMaster and chatMessages , i was trying to left join the two tables to get all data from master and latest message for each chat. I am using this query to join the two tables
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn FROM chatMaster c
left join chatMessages m on c.id = m.id group by m.id ;
This query is for selecting only one row for each entry in chatMaster, but it is selecting the first message for every entry like,
1, 2018-11-24 00:40:08, HI!, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001
2, 2018-11-24 00:40:21, HI! There, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1
3, 2018-11-24 01:33:12, HI!, e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2
this is the result of select * from chatMessages, There are two entries for chat id 99e22056-ee7f-11e8-bc28-8acac6f59ef9 and i want the send one which is send on 2018-11-24 00:40:21 but the result of my join query is
99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001, 1, 1001, HI!, 2018-11-24 00:40:08
e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2, 10001, 2, HI!, 2018-11-24 01:33:12
It is selecting the first message. How to select the latest message? What changes should i make to get the latest message from chatMessages instead of first message. please ask if you need more details
mysql
asked Nov 23 '18 at 20:16
SonySony
3,86332639
I have two tables named chatMaster and chatMessages , i was trying to left join the two tables to get all data from master and latest message for each chat. I am using this query to join the two tables
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn FROM chatMaster c
left join chatMessages m on c.id = m.id group by m.id ;
This query is for selecting only one row for each entry in chatMaster, but it is selecting the first message for every entry like,
1, 2018-11-24 00:40:08, HI!, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001
2, 2018-11-24 00:40:21, HI! There, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1
3, 2018-11-24 01:33:12, HI!, e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2
this is the result of select * from chatMessages, There are two entries for chat id 99e22056-ee7f-11e8-bc28-8acac6f59ef9 and i want the send one which is send on 2018-11-24 00:40:21 but the result of my join query is
99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001, 1, 1001, HI!, 2018-11-24 00:40:08
e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2, 10001, 2, HI!, 2018-11-24 01:33:12
It is selecting the first message. How to select the latest message? What changes should i make to get the latest message from chatMessages instead of first message. please ask if you need more details
mysql
mysql
asked Nov 23 '18 at 20:16
SonySony
3,86332639
asked Nov 23 '18 at 20:16
SonySony
3,86332639
asked Nov 23 '18 at 20:16
SonySony
3,86332639
asked Nov 23 '18 at 20:16
SonySony
3,86332639
asked Nov 23 '18 at 20:16
SonySony
3,86332639
3,86332639
Why are you left joining ? I doubt a chatmaster can exist without any chatmessages in it ?
– Madhur Bhaiya
Nov 23 '18 at 20:18
1
Possible duplicate of MySQL: Get latest message from 2 tables that are associated with eachother
– Madhur Bhaiya
Nov 23 '18 at 20:19
Because when a chat request is accepted an entry is made in master table and at that time there will not be any entry in chatMessages
– Sony
Nov 23 '18 at 20:19
Check these answers: stackoverflow.com/a/53391638/2469308 and stackoverflow.com/a/53090014/2469308
– Madhur Bhaiya
Nov 23 '18 at 20:22
What is your MySQL server version ? Do you have access to the version 8.0+ ? Solution complexity will be significantly less in newer versions (with Window functions).
– Madhur Bhaiya
Nov 23 '18 at 20:24
|
show 1 more comment
Why are you left joining ? I doubt a chatmaster can exist without any chatmessages in it ?
– Madhur Bhaiya
Nov 23 '18 at 20:18
1
Possible duplicate of MySQL: Get latest message from 2 tables that are associated with eachother
– Madhur Bhaiya
Nov 23 '18 at 20:19
Because when a chat request is accepted an entry is made in master table and at that time there will not be any entry in chatMessages
– Sony
Nov 23 '18 at 20:19
Check these answers: stackoverflow.com/a/53391638/2469308 and stackoverflow.com/a/53090014/2469308
– Madhur Bhaiya
Nov 23 '18 at 20:22
What is your MySQL server version ? Do you have access to the version 8.0+ ? Solution complexity will be significantly less in newer versions (with Window functions).
– Madhur Bhaiya
Nov 23 '18 at 20:24
Why are you left joining ? I doubt a chatmaster can exist without any chatmessages in it ?
– Madhur Bhaiya
Nov 23 '18 at 20:18
Why are you left joining ? I doubt a chatmaster can exist without any chatmessages in it ?
– Madhur Bhaiya
Nov 23 '18 at 20:18
1
1
Possible duplicate of MySQL: Get latest message from 2 tables that are associated with eachother
– Madhur Bhaiya
Nov 23 '18 at 20:19
Possible duplicate of MySQL: Get latest message from 2 tables that are associated with eachother
– Madhur Bhaiya
Nov 23 '18 at 20:19
Because when a chat request is accepted an entry is made in master table and at that time there will not be any entry in chatMessages
– Sony
Nov 23 '18 at 20:19
Because when a chat request is accepted an entry is made in master table and at that time there will not be any entry in chatMessages
– Sony
Nov 23 '18 at 20:19
Check these answers: stackoverflow.com/a/53391638/2469308 and stackoverflow.com/a/53090014/2469308
– Madhur Bhaiya
Nov 23 '18 at 20:22
Check these answers: stackoverflow.com/a/53391638/2469308 and stackoverflow.com/a/53090014/2469308
– Madhur Bhaiya
Nov 23 '18 at 20:22
What is your MySQL server version ? Do you have access to the version 8.0+ ? Solution complexity will be significantly less in newer versions (with Window functions).
– Madhur Bhaiya
Nov 23 '18 at 20:24
What is your MySQL server version ? Do you have access to the version 8.0+ ? Solution complexity will be significantly less in newer versions (with Window functions).
– Madhur Bhaiya
Nov 23 '18 at 20:24
|
show 1 more comment
1 Answer
1
active
oldest
votes
In a Derived Table, you can get the maximum values of createdOn (latest createdOn) for every chat-master id. You can then join this result-set to the main table, to get only the row corresponding to latest createdOn.
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn
FROM chatMaster c
LEFT JOIN
( SELECT id, MAX(createdOn) AS latest_createdOn
FROM chatMessages
GROUP BY id
) AS dt
ON dt.id = c.id
LEFT JOIN chatMessages m
ON m.id = c.id AND
m.createdOn = dt.latest_createdOn
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
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%2f53452503%2fhow-to-select-distinct-rows-when-using-joint-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In a Derived Table, you can get the maximum values of createdOn (latest createdOn) for every chat-master id. You can then join this result-set to the main table, to get only the row corresponding to latest createdOn.
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn
FROM chatMaster c
LEFT JOIN
( SELECT id, MAX(createdOn) AS latest_createdOn
FROM chatMessages
GROUP BY id
) AS dt
ON dt.id = c.id
LEFT JOIN chatMessages m
ON m.id = c.id AND
m.createdOn = dt.latest_createdOn
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
add a comment |
In a Derived Table, you can get the maximum values of createdOn (latest createdOn) for every chat-master id. You can then join this result-set to the main table, to get only the row corresponding to latest createdOn.
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn
FROM chatMaster c
LEFT JOIN
( SELECT id, MAX(createdOn) AS latest_createdOn
FROM chatMessages
GROUP BY id
) AS dt
ON dt.id = c.id
LEFT JOIN chatMessages m
ON m.id = c.id AND
m.createdOn = dt.latest_createdOn
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
add a comment |
In a Derived Table, you can get the maximum values of createdOn (latest createdOn) for every chat-master id. You can then join this result-set to the main table, to get only the row corresponding to latest createdOn.
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn
FROM chatMaster c
LEFT JOIN
( SELECT id, MAX(createdOn) AS latest_createdOn
FROM chatMessages
GROUP BY id
) AS dt
ON dt.id = c.id
LEFT JOIN chatMessages m
ON m.id = c.id AND
m.createdOn = dt.latest_createdOn
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
In a Derived Table, you can get the maximum values of createdOn (latest createdOn) for every chat-master id. You can then join this result-set to the main table, to get only the row corresponding to latest createdOn.
SELECT c.id, c.p1, c.p2, m.toP, m.message, m.createdOn
FROM chatMaster c
LEFT JOIN
( SELECT id, MAX(createdOn) AS latest_createdOn
FROM chatMessages
GROUP BY id
) AS dt
ON dt.id = c.id
LEFT JOIN chatMessages m
ON m.id = c.id AND
m.createdOn = dt.latest_createdOn
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 23 '18 at 20:31
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
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%2f53452503%2fhow-to-select-distinct-rows-when-using-joint-table%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
Why are you left joining ? I doubt a chatmaster can exist without any chatmessages in it ?
– Madhur Bhaiya
Nov 23 '18 at 20:18
1
Possible duplicate of MySQL: Get latest message from 2 tables that are associated with eachother
– Madhur Bhaiya
Nov 23 '18 at 20:19
Because when a chat request is accepted an entry is made in master table and at that time there will not be any entry in chatMessages
– Sony
Nov 23 '18 at 20:19
Check these answers: stackoverflow.com/a/53391638/2469308 and stackoverflow.com/a/53090014/2469308
– Madhur Bhaiya
Nov 23 '18 at 20:22
What is your MySQL server version ? Do you have access to the version 8.0+ ? Solution complexity will be significantly less in newer versions (with Window functions).
– Madhur Bhaiya
Nov 23 '18 at 20:24