Here is the table I'm currently working with.
---- ------------- ------------ ---------
| id | number_from | number_to | message |
---- ------------- ------------ ---------
| 1 | 5365323350 | 5368177083 | Hello |
| 2 | 5368177083 | 5365323350 | HRU |
| 3 | 5363916551 | 5365323350 | Hola |
| 4 | 5365323350 | 5363916551 | Howdy |
---- ------------- ------------ ---------
What I am wanting to do is select all columns where number_from = 5365323350 or number_to = 5365323350. However I only want to show one the latest row based on the opposite columns. Kind of hard to explain so below is what I want the results to look like
---- ------------- ------------ ---------
| id | number_from | number_to | message |
---- ------------- ------------ ---------
| 2 | 5368177083 | 5365323350 | HRU |
| 4 | 5365323350 | 5363916551 | Howdy |
---- ------------- ------------ ---------
So here are the latest rows where they are no duplicates following the where part of it.
I'm pretty new to SQL and I've done hours of searching and haven't come up with a way to do this yet so hoping to get some help.
CodePudding user response：
NOTE: the first part of this is a generalized solution, showing the latest message in any conversation. The solution for a specific phone number is at the end.
If it were me, I think I might try to come up with some kind of unique identifier to use to aggregate the ids over so I can find the last one for any grouping. Take a look at this fiddle:
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=11a246693a7c61455b2dd17b144eb472
Here I have used CONCAT to create a field with the two phone numbers, and have used LEAST and GREATEST to make sure they are ordered. Then I can use that combination of phone numbers to find the greatest id that has the same combination:
SELECT
concat(
greatest(number_from, number_to),
least(number_from, number_to)
) as chatID,
max(id) as lastID
FROM table1
GROUP BY 1;
I use GROUP BY 1 here because mySQL allows you to specify the grouping by column number, and I don't want to have to repeat the concatenation in the clause, since it's somewhat unwieldy. You might also do this with a subquery, so that you can group on a readable value:
SELECT chatID, max(id) as lastID
FROM (
SELECT id, concat(
greatest(number_from, number_to),
least(number_from, number_to)
) as chatID
FROM table1
) z GROUP BY chatID;
Now that you have the ids for the last entry in the chat, you just have to use that as a parameter to IN to return the entries in table1 that match those ids. The subquery has to be rewritten slightly, since it has two columns and IN expects one operand. Here's the one with the subselect, since it's probably a little easier to read if someone came across it:
SELECT * FROM table1 WHERE id IN (
SELECT max(id) FROM (
SELECT id, concat(
greatest(number_from, number_to),
least(number_from, number_to)
) as chatID
FROM table1
) z GROUP BY chatID
);
but you could just as easily use the one without the subselect:
SELECT * FROM table1 WHERE id IN (
SELECT max(id)
FROM table1
group by concat(
greatest(number_from, number_to),
least(number_from, number_to)
)
);
You could also use a join with the ids, like so:
SELECT t1.*
FROM table1 t1
JOIN (
SELECT
concat(
greatest(number_from, number_to),
least(number_from, number_to)
) as chatID,
max(id) as lastID
FROM table1
GROUP BY 1
) ids on t1.id = ids.lastID;
or
SELECT t1.*
FROM table1 t1
JOIN (
SELECT
concat(
greatest(number_from, number_to),
least(number_from, number_to)
) as chatID,
max(id) as lastID
FROM table1
GROUP BY 1
) ids on t1.id = ids.lastID;
So, it's quite simple to put specifications for specific phone numbers. You add them to the subquery that is fetching the max(id). I'm only going to do it for one of the queries, since the modification is the same for all versions:
SELECT * FROM table1 WHERE id IN (
SELECT max(id) FROM (
SELECT id, concat(
greatest(number_from, number_to),
least(number_from, number_to)
) as chatID
FROM table1
WHERE number_to = '5365323350'
OR number_from = '5365323350'
) z GROUP BY chatID
);
These queries all use the extended dataset from the fiddle you provided in a comment. You can use explain to see the execution plan. They all evaluate the same.
Let me know if this helps.
CodePudding user response：
if I understood your question correctly, you could try something like this
SELECT tb1.id, sub_tb1.number_from, sub_tb1.number_to, tb1.message
FROM table1 tb1
JOIN
(select max(number_from) as number_from, number_to
from table1
WHERE number_to = 5365323350
group by number_to
)as sub_tb1 on tb1.number_from = sub_tb1.number_from
UNION ALL
SELECT tb1.id, sub_tb1.number_from, sub_tb1.number_to, tb1.message
FROM table1 tb1
JOIN
(select number_from, min(number_to) as number_to
from table1
WHERE number_from = 5365323350
group by number_from
)as sub_tb1 on tb1.number_to = sub_tb1.number_to
CodePudding user response：
What it SOUNDS like you are trying to describe is like a chat between two people, A and B. A initiates chat to B via ID = 1, B responds to A via ID = 2. So, of the pair, you want whatever the highest ID is for the chat conversation. Dont know actual context, but sample appears like what you are trying to describe. From the most recent ID, you want THAT row's values.
Revised and working query
select
yt2.*
from
table1 yt2
JOIN
( select
case when yt.number_from = 5365323350
then yt.number_to
else yt.number_from end ResponseTo,
max( yt.id ) lastReplyID
from
table1 yt
where
yt.number_from = 5365323350
OR yt.number_to = 5365323350
group by
case when yt.number_from = 5365323350
then yt.number_to
else yt.number_from end ) PQ
on yt2.id = PQ.LastReplyID
In this query, the inner PRE-QUERY (pq alias), I am grabbing the records only associated with the user in question by qualifying that user as the from or to person. The column grouped by is via a case/when. If the from is the person in question, grab the TO person. If the TO person is the user, grab the FROM person the reply is against. For that specific person, and conversation to the user in question, grab the latest ID for said conversation.
Once that is done, join back to the original table based on that latest ID per the given chat conversation going on.