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_ids 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 term_taxonomy_id

this is a syntax error

SELECT * 
    FROM wp_term_relationships
    WHERE term_taxonomy_id IN (20, 21, 22)
    ORDER BY object_id DESC
    GROUP BY term_taxonomy_id

Answer 1

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?

Answer 2

GROUP BY should be used with aggregate functions such as count, sum, avg etc. Such as:

 select product_name, sum(price) 
 from product_sales
 group by product_name

Answer 3

Non of the examples you have posted are correct T-SQL. You cannot SELECT * when using a GROUP BY clause.

GROUP BY does not return the first record in the group - it aggregates by the columns specified in the clause (there are also the columns you can use in your SELECT clause).

You need to use a aggregate function (such as SUM or COUNT or MAX) in your SELECT clause. You did not specify what kind of aggregate you are trying to get, so I will use COUNT in my example, for the number of records:

SELECT COUNT(term_taxonomy_id)
FROM wp_term_relationships
WHERE term_taxonomy_id IN (20, 21, 22)
GROUP BY term_taxonomy_id

Answer 4

I don't want to repeat what the other answers say, but would like to add a little info on SQL grouping, which may help...

think about the query result being built in this order:

FROM & JOIN determine & filter rows
WHERE more filters on those rows
GROUP BY combines those rows into groups (now one row per the group)
HAVING filters groups at a group level
ORDER BY arranges the remaining rows/groups