MySQL SELECT Distinct on single column not entire query

Question

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

possible duplicate of [SQL - Select distinct but return all columns?](http://stackoverflow.com/questions/6127338/sql-select-distinct-but-return-all-columns) — scrowler, Nov 17 '13 at 23:41
I don't want to select * though, there are over 75 columns, I only need a handful of columns. — Rocco The Taco, Nov 17 '13 at 23:42
So you specify your columns instead of using `*` and still use `GROUP BY` at the end instead of `DISTINCT` in your select — scrowler, Nov 17 '13 at 23:43
@RoccoTheTaco why not join the tables with a (SELECT DISTINCT DevelopmentDisplay FROM RESI) T2 ? — Roger, Nov 17 '13 at 23:47
nah, no such luck. I return 24 results with my query and over a 1000 when I use Group BY...its not the same. — Rocco The Taco, Nov 17 '13 at 23:47
@Rogier that sound compelling, can you provide an example of what you mean? can you join a table on itself? — Rocco The Taco, Nov 17 '13 at 23:48

score 1 · Accepted Answer · answered Nov 18 '13 at 00:29

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);

MySQL SELECT Distinct on single column not entire query

1 Answers1