CODEWITHSUNDEEP
mysql
Rocco The Taco
Rocco The Taco

Reputation: 3797

MySQL SELECT Distinct on single column not entire query

On this solution the user is querying on id to display other columns in the select but query only distinct on a single column. I've tried to do this but can't get the syntax right. Here is the query but I only want distinct on DevelopmentDisplay column.

SELECT DISTINCT
`RESI`.`DevelopmentDisplay`,
`RESI`.`City`,
`RESI`.`ZipCode`
FROM
`RESI`
WHERE
`RESI`.`ZipCode` =  '91263'
ORDER BY
`RESI`.`DevelopmentDisplay` ASC

Upvotes: 0

Views: 215

Answers (1)

Roger
Roger

Reputation: 7610

Yes you can self join tables.

I see two options like this. However i would suggest you index DevelopmentDisplay column. Depending on the number of records it can get very slow.

SELECT 
    t1.DevelopmentDisplay,
    t1.City,
    t1.ZipCode
FROM
    RESI t1,
    (SELECT DISTINCT DevelopmentDisplay FROM RESI) t2

WHERE
    t1.ZipCode =  '91263' AND
    t1.DevelopmentDisplay = t2.DevelopmentDisplay;

Alternatively:

SELECT 
    t1.DevelopmentDisplay,
    t1.City,
    t1.ZipCode
FROM
    RESI t1,
WHERE
    t1.ZipCode =  '91263' AND
    t1.DevelopmentDisplay IN (SELECT DISTINCT DevelopmentDisplay FROM RESI);

Upvotes: 1

Related Questions