Specifying which record to return from a GROUP BY clause

Question

EDIT TO CLARIFY I am probably misunderstanding the use of `GROUP BY` so I'll just rephrase my question without making assumptions on how to solve the problem: I have a list of `term_id`s and a table containing objects (which have an `object_id` PK and `term_id` as FK among other fields), I need to extract the object with the highest `object_id` for every `term_id` supplied. What's the correct way to do it? ORIGINAL QUESTION I'm sure I'm missing something obvious but I can't figure out how to specify which record will be returned by a query with a `GROUP BY`. By default `GROUP BY` returns the first record in the group, who can I get the last one instead without using a subquery? Basic query returns first record: ``` SELECT * FROM wp_term_relationships WHERE term_taxonomy_id IN (20, 21, 22) GROUP BY term_taxonomy_id ``` this works, but with a subquery ``` SELECT * FROM ( SELECT * FROM wp_term_relationships WHERE term_taxonomy_id IN (20, 21, 22) ORDER BY object_id DESC ) wtt GROUP BY ter…

Accepted Answer

`SELECT *... GROUP BY` is not supposed to work. The fact that your first example works is a screwy feature of MySQL.

To make GROUP BY work, your SELECT clause can't be `*`. It has to be a mixture of the GROUP BY columns and "aggregate" functions like `COUNT`, `SUM`, etc.

`SELECT COUNT(*), some_column FROM... GROUP BY some_column` is the expected form.

`SELECT *` is not expected to work.

You want to find the highest object_id for each term_id.

```
SELECT MAX(term_id), object_id FROM some_table GROUP BY object_id
```

Something like that?

Specifying which record to return from a GROUP BY clause

Solution

Problem