GROUP BY different column conditionally

Question

I want to show a list of chat messages.

For this I have a SELECT statement:

SELECT id, `from`, sent, message, recd FROM chat 
WHERE id IN(
  SELECT MAX(id) FROM chat
  WHERE `to` = ? GROUP BY `from`
)
ORDER BY id DESC

The problem is, I want to select messages that to = maria and from = maria conditionally.

That is, if to = maria I want to group by from, and if from = maria, I want to group by to.

How can I change this GROUP BY dynamically?

Answer 1

I would got for a UNION.

(SELECT id, 'from' as direction, m_from AS fromto, sent, message, recd
FROM chat WHERE id IN (SELECT MAX(id) FROM chat WHERE to = 'Maria' GROUP BY m_from))
UNION
(SELECT id, 'to' as direction, m_to AS fromto, sent, message, recd FROM chat
WHERE id IN (SELECT MAX(id) FROM chat WHERE m_from = 'Maria' GROUP BY to))

If you select MAX(id) an ORDER BY id statement is not necessary.

You can add extra fields in the SELECT clause as I did with 'direction'.