add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `phone_messages`
(`character_id` varchar(10), `target_character_id` varchar(10),
`message` varchar(100), `type` varchar(10), `date` datetime);

INSERT INTO `phone_messages` (`character_id`, `target_character_id`, `message`, `type`, `date`)
VALUES
("22", "33", "correct", "text", "2020-08-25 23:28:31"),
("33", "22", "perfect see you then", "text", "2020-08-25 23:28:43"),
("57", "22", "where is this mega mall", "text", "2020-09-05 19:05:25"),
("22", "57", "the tool shop down south", "text", "2020-09-05 19:05:45");


SELECT * FROM `phone_messages`

character_id target_character_id message type date
22 33 correct text 2020-08-25 23:28:31
33 22 perfect see you then text 2020-08-25 23:28:43
57 22 where is this mega mall text 2020-09-05 19:05:25
22 57 the tool shop down south text 2020-09-05 19:05:45
SET @character_id = 22;
SET @t = 'text';

/* Demonstration of what the CTE does */

SELECT *,
@character_id AS `primary_character_id`,
CASE
WHEN `character_id` = @character_id THEN `target_character_id`
ELSE `character_id` END AS `second_character_id`
FROM
`phone_messages`
WHERE
(`character_id`= @character_id OR `target_character_id`= @character_id)
AND (`type`= @t );
character_id target_character_id message type date primary_character_id second_character_id
22 33 correct text 2020-08-25 23:28:31 22 33
33 22 perfect see you then text 2020-08-25 23:28:43 22 33
57 22 where is this mega mall text 2020-09-05 19:05:25 22 57
22 57 the tool shop down south text 2020-09-05 19:05:45 22 57
/* Answer */

WITH Call_list AS
(SELECT *,
@character_id AS `primary_character_id`,
CASE
WHEN `character_id` = @character_id THEN `target_character_id`
ELSE `character_id` END AS `second_character_id`
FROM
`phone_messages`
WHERE
(`character_id`= @character_id OR `target_character_id`= @character_id)
AND (`type`= @t )
),
Sorted_Call_List AS
(SELECT *,
ROW_NUMBER() OVER
(PARTITION BY `primary_character_id`, `second_character_id`
ORDER BY `Date` DESC
) AS char_rn
FROM Call_list
)
SELECT `character_id`, `target_character_id`, `message`, `type`, `date`
FROM Sorted_Call_List
WHERE char_rn = 1;

character_id target_character_id message type date
33 22 perfect see you then text 2020-08-25 23:28:43
22 57 the tool shop down south text 2020-09-05 19:05:45
/* Previous answer (latest incoming and outgoing texts for 22) */

WITH sorted_calls AS
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY `character_id` ORDER BY `Date` DESC) AS char_rn,
ROW_NUMBER() OVER (PARTITION BY `target_character_id` ORDER BY `Date` DESC) AS target_char_rn
FROM
`phone_messages`
WHERE
(
`character_id`= @character_id
OR `target_character_id`= @character_id
)
AND (`type`= @t )
)
SELECT character_id, target_character_id, message, `type`, `date`
FROM `sorted_calls`
WHERE (char_rn = 1 AND `character_id`= @character_id)
OR (target_char_rn = 1 AND `target_character_id`= @character_id)
;

character_id target_character_id message type date
57 22 where is this mega mall text 2020-09-05 19:05:25
22 57 the tool shop down south text 2020-09-05 19:05:45