SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS "contacts" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "name" text NOT NULL
);
✓
CREATE TABLE IF NOT EXISTS "messages" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "date" integer NOT NULL,
  "senderId" integer NOT NULL,
  "recipientId" integer NOT NULL,
  "text" text NOT NULL
);
✓
INSERT INTO contacts (`id`, `name`) VALUES
  ('1', 'A'),
  ('2', 'B'),
  ('3', 'C');
✓
INSERT INTO messages (`id`, `date`, `senderId`, `recipientId`, `text`) VALUES
  ('1', '10', '1', '2', 'A→B @ 10'),
  ('2', '20', '2', '1', 'B→A @ 20'),
  ('3', '30', '1', '2', 'A→B @ 30'),
  ('4', '40', '1', '3', 'A→C @ 40'),
  ('5', '50', '3', '1', 'C→A @ 50'),
  ('6', '60', '2', '3', 'B→C @ 60'),
  ('7', '70', '3', '2', 'C→B @ 70');
✓
SELECT c2.name, MAX(m.date) last_date, m.text
FROM messages m 
INNER JOIN contacts c1 ON c1.id IN (m.senderId, m.recipientId)
INNER JOIN contacts c2 ON c2.id IN (m.senderId, m.recipientId) AND c1.id <> c2.id
WHERE c1.name = 'A'
GROUP BY c2.id
ORDER BY last_date DESC;


name
last_date
text


C
50
C→A @ 50

B
30
A→B @ 30


SELECT c2.name, MAX(m.date) last_date, m.text
FROM messages m 
INNER JOIN contacts c1 ON c1.id IN (m.senderId, m.recipientId)
INNER JOIN contacts c2 ON c2.id IN (m.senderId, m.recipientId) AND c1.id <> c2.id
WHERE c1.name = 'B'
GROUP BY c2.id
ORDER BY last_date DESC;


name
last_date
text


C
70
C→B @ 70

A
30
A→B @ 30


SELECT c2.name, MAX(m.date) last_date, m.text
FROM messages m 
INNER JOIN contacts c1 ON c1.id IN (m.senderId, m.recipientId)
INNER JOIN contacts c2 ON c2.id IN (m.senderId, m.recipientId) AND c1.id <> c2.id
WHERE c1.name = 'C'
GROUP BY c2.id
ORDER BY last_date DESC;


name
last_date
text


B
70
C→B @ 70

A
50
C→A @ 50

`name`	`last_date`	`text`
`C`	`50`	`C→A @ 50`
`B`	`30`	`A→B @ 30`

`name`	`last_date`	`text`
`C`	`70`	`C→B @ 70`
`A`	`30`	`A→B @ 30`

`name`	`last_date`	`text`
`B`	`70`	`C→B @ 70`
`A`	`50`	`C→A @ 50`