How to select only one row with the desired values?

Hello everybody,
There is a table with users, where each user has a id
and there is a table with messages, where each message is a string and has two fields for the id of the sender. There are many messages, some are the same on both id.
Example:
5e9a2b1c8dbd5787159211.png
Where "id" is a sequence number of the message, "fsender_id" - id of the sender, and "ssender_id" - id of the recipient.
For example, running a query for user with id = 1. Then all the messages sent to them or he will have fsender_id = 1 or ssender_id = 1. Then you need to select from the table only those rows where fsender_id ssender_id or equal to 1, and where id is the second user (or fsender_id ssender_id) is not repeated. That is, in the following table, the result should only be the rows with id 1 and 6.
April 19th 20 at 12:37

Related questions

More answers about "How to select only one row with the desired values?"
3 answers
April 19th 20 at 12:39
Solution
Select using where clause of the message, where the user is the sender or recipient.
Then group the "the contractor" and the group choose a bigger number for the package (if you still need the last, not the first message).

scheme
CREATE TABLE m (
 id INTEGER(11) NOT NULL AUTO_INCREMENT,
 u_from INTEGER(11) NOT NULL,
 u_to INTEGER(11) NOT NULL,
 PRIMARY KEY USING BTREE (id)
) ENGINE=InnoDB
AUTO_INCREMENT=8 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

INSERT INTO `m` (id, u_from, u_to) VALUES
(1,7,1),
(2,7,1),
(3,7,1),
(4,7,1),
(5,1,7),
(6,1,4),
(7,4,1);
COMMIT;


select
 max(id) id,
 if(u_from = 1, concat('TO ', u_to), concat('FROM ', u_from)) last_msg,
u_from,
u_to
from m
where u_from = 1 or u_to = 1
group by if(u_from = 1, u_to, u_from)
order by id
Thank you for such a detailed reply)
Indeed, the result is the one that I want)
I understand that it's weird, but I need the first message, so refined, everything works)))
Thank you very much ! - gaylord.DuBuq commented on April 19th 20 at 12:42
In my opinion, is not quite taken into consideration a couple of points:

1. If you have such data, the result is not quite correct:

Test

(1, 7, 1),
(2, 7, 1),
(3, 7, 1),
(4, 7, 1),
(5, 1, 7),
(6, 1, 4),
(7, 4, 1),
(8, 3, 1),
(9, 1, 3),
(10, 3, 2),
(11, 2, 3),
(12, 9, 8);


2. You need to take into account not only the id = 1: u_from = 1 or u_to = 1, but other id, we need a more complex grouping. - afton.Boyle74 commented on April 19th 20 at 12:45
@jovan.Bartell88, I put in a request id of an authorized user, so that takes into account not only the id = 1. - gaylord.DuBuq commented on April 19th 20 at 12:48
@Gustave_Schamberger, if you are using the script make a query call with variable substitution id, then my comment above is removed. - afton.Boyle74 commented on April 19th 20 at 12:51
@jovan.Bartell88, Right) Via script) - gaylord.DuBuq commented on April 19th 20 at 12:54
April 19th 20 at 12:41
SELECT `user_id`, MAX(`id`)
 FROM (
 SELECT `id`, `fsender_id` AS `user_id`
 FROM `messages`
 WHERE `ssender_id` = :iserId
 UNION SELECT `id`, `ssender_id`
 FROM `messages`
 WHERE `fsender_id` = :iserId
 ) AS `t`
 GROUP BY `user_id`
April 19th 20 at 12:43
I'm honestly trying to understand how to work Your logic, but almost podganyali answer
i.e. Your answer would be.
select min(id), case when fsender_id = 1 then ssender_id
 when ssender_id = 1 then fsender_id end other_id
 from users
where fsender_id = 1 or ssender_id = 1
group by other_id
But if aydishnikov will be a lot? - afton.Boyle74 commented on April 19th 20 at 12:46

Find more questions by tags MySQL