CODEWITHSUNDEEP
mysqlsql
joeschmidt45
joeschmidt45

Reputation: 1962

MYSQL Query with distinct and max

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

Answers (2)

Mark Byers
Mark Byers

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

Colselaw
Colselaw

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

Related Questions