CODEWITHSUNDEEP
mysql
JVMX
JVMX

Reputation: 1078

Selecting entire Record for Distinct Column in MySql

I have a table:

[myTable]
ID
Name
HairColor
NumberOfPairsOfPants

I can easily get a list of HairColor with

SELECT DISTINCT HairColor FROM myTable

But I want the full contents the records where the hair color is Distinct (Yes the database table is denormalized/redundant so I don't get logic errors)

Pseudo code

SELECT DISTINCT HairColor,* FROM myTable

Syntax help!

Upvotes: 0

Views: 100

Answers (1)

Evan Volgas
Evan Volgas

Reputation: 2911

Okay, first off, the SELECT DISTINCT haircolor FROM myTable doesn't give you "Where haircolor is distinct." It gives you all the distinct hair colors. Kind of like "Distinct names in the room." If there are two people named Sally, SELECT DISTINCT name would give you one row for Sally. What you're looking for is a bit different.

What you want to do is, first off all, determine which hair colors are distinct, ie, which ones occur only once. For that you will need 

SELECT haircolor, COUNT(*) AS cnt FROM myTable GROUP BY haircolor HAVING cnt > 1;

Once you've done that, you will want to join those results with your original table to get the entire rows associated with those hair colors, eg

SELECT a.* FROM myTable AS a
INNER JOIN 
 (SELECT SELECT haircolor, COUNT(*) AS cnt FROM myTable GROUP BY haircolor HAVING cnt > 1) AS b
ON a.haircolor = b.haircolor

Upvotes: 1

Related Questions