Group By Except For Certain Value
You can't easily do this with one statement but you can
UNION the results of two statements
GROUPthe list of all but
- Add (
UNION) the list of all
SELECT MIN(id), name, parent_id, school_idFROM YourTableWHERE name <> 'unknown'GROUP BY name, parent_id, school_idUNION ALLSELECT id, name, parent_id, school_idFROM YourTableWHERE name = 'unknown'
Note that I assume you have posted wrong
unknown id's in your result
As a single query...
SELECT MIN(id) AS id, name, parent_id, school_idFROM yourTableGROUP BY CASE WHEN name = 'unknown' THEN id ELSE 0 END, name, parent_id, school_id
GROUP BY CASE WHEN name <> 'unknown' THEN name ELSE CAST(id AS VARCHAR(???)) END, parent_id, school_id-- Where VARCHAR(???) is the data type of the `name` field.-- Also assumes no value in `name` is the same as an id for an 'unknown' field
Both avoid UNION and the overhead of parsing the table twice, replacing it with a slightly increased complexity