Reputation: 1962
I need a mysql query which will select all rows that contain "joe" in either the "to" or "from" column. It needs to be distinct among the "to" and "from" columns, which means that it does not matter what order the names appear in. i.e. "joe", "bob" should be treated as the same as "bob", "joe". If there are duplicates among a pair of names, choose the one with the max id. The final table should also be sorted by id descending. For example, running the query on the below table:
Before:
id to from
---|-----|------
1 | joe | bob |
2 | bob | hal |
3 | joe | joe |
4 | sue | joe |
5 | bob | bob |
6 | bob | joe |
7 | hal | hal |
8 | joe | bob |
After:
id to from
---|-----|------
8 | joe | bob |
4 | sue | joe |
3 | joe | joe |
Upvotes: 2
Views: 138
Reputation: 839114
Try a GROUP BY where you group by (from, to) sorted in alphabetical order:
SELECT id, `from`, `to`
FROM yourtable
WHERE id IN
(
SELECT MAX(id)
FROM yourtable
WHERE `from` = 'joe' OR `to` = 'joe'
GROUP BY LEAST(`from`, `to`), GREATEST(`from`, `to`)
)
ORDER BY id DESC
See it working online: sqlfiddle
Upvotes: 2
Reputation: 1079
Hrmm...I can get close with:
SELECT inside.id, inside.`to` , inside.`from`
FROM (
SELECT MAX( id ) id, `to` , `from`
FROM people
WHERE `to` = 'joe'
OR `from` = 'joe'
GROUP BY `to` , `from`
)inside
ORDER BY inside.id DESC
But that doesn't quite give the results you want because it still has dupes when the to and from are swapped.
I was trying with some UNION
's, but that actually ends up complicating the problem because it ends up creating more unique columns (I would swap the order of to
and from
to get all the joe's into the same column). Once you've got the right unique rows without taking into account that to
and from
matters, it's harder getting it back to where to
and from
don't matter.
Maybe this will get you started while I puzzle over it some more.
Upvotes: 0