Return IDs with a single value in another field

ttist25
ttist25 used Ask the Experts™
on
Working in SQL Server 2012.

I'm trying to find rows in my table where an ID exists with only 1 distinct value in another field:

My table looks something like this:
CREATE TABLE #tmpMyTable (
      MyID int NULL,
      MyField char NULL
      )
INSERT INTO #tmpMyTable
      VALUES      (1, 'A'),
                  (1, 'B'),
                  (1, 'B'),
                  (2, 'A'),
                  (3, 'A'),
                  (3, 'B'),
                  (3, 'B'),
                  (3, 'C'),
                  (4, 'A'),
                  (4, 'A'),
                  (5, 'D');

The query I'm trying to build would return the following:

2, A
4, A
5, D

I feel like this should be fairly simple but I'm failing miserably at it.  I've tried a SELF JOIN, GROUP BY, OVER PARTITION BY, etc. and I can't seem to get it.  

Any help will be greatly appreciated!

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT MyID, MIN(MyField) AS MyField
FROM #tmpMyTable
GROUP BY MyID
HAVING COUNT(DISTINCT MyField) = 1
ORDER BY MyID

Author

Commented:
Thanks again  Scott!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial