Group By Except For Certain Value

You can't easily do this with one statement but you can UNION the results of two statements

• GROUP the list of all but unknown
• Add (UNION) the list of all unknown

SQL Statement

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

Or possibly...

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 GROUP BY.

SELECT  MIN(id), name, parent_id, school_id  FROM  TableWHERE   name <> 'unknown'GROUP BY name, parent_id, school_idUNION ALLSELECT  id, name, parent_id, school_id  FROM  Table WHERE  name = 'unknown'