Get Latest SQL Query Clarification

Question

I stole this query below from this thread Retrieving the last record in each group :

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

Could someone please help me understand how AND m1.id < m2.id returns the correct result/ I thought it should be AND m1.id > m2.id which doesn't return correct result.

I'm sort of wondering technically id should be greater.

Answer 1

You're looking for instances that don't join, given WHERE m2.id IS NULL.

Consider the following pairs of id's:

m1_id  m2_id
1      1
2      2
3      3

Using m1.id < m2.id without your WHERE criteria gets you:

m1_id  m2_id
1      2
1      3
2      3
3      NULL

• 1 is not less than 1, but it is less than 2 and 3, so 1 joins to multiple records.
• 2 is not less than 1 or 2, but it is less than 3, so 2 joins to 1 record.
• 3 is not less than any of the records, so it does not join. It returns NULL which is the criteria you're looking for in your WHERE clause.