I am trying to write a query that tells me every distinct code in my table that has more than 1 distinct display associated with it. Example:
code display
1 North
2 East
2 South
2 South
3 East
4 West
4 North
In that example I would be looking to return 2 and 4 since those two are the only codes with more than 1 distinct display.
What I have so far is below, but I am honestly not sure if I am even in the right direction. Some guidance would be much appreciated!
select code, display
from directions
group by code where count(distinct display) > 1
Use HAVING COUNT(DISTINCT display) > 1:
SELECT code
FROM directions
GROUP BY code
HAVING COUNT(DISTINCT display) > 1;
Online demo: https://onecompiler.com/mysql/42t5dxy6v
select distinct code, display from directories gives table of distinct pairs. Now:
select code, count(*) from (select distinct code, display from directories) as a group by code having count(*)>1 is probably the answer.
Actually – going with count distinct can be expensive, that is why I’ve proposed sub select solution, or – maybe – that was already optimised?