Specifying a record to return from a GROUP BY clause

Question

CHANGE TO CLOSE

Perhaps I misunderstand the use GROUP BY, so I simply rephrase my question without making assumptions on how to solve the problem:

I have a list term_idand a table containing objects (which have object_idPK and term_idlike FK among other fields), I need to extract the object with the highest object_idfor each term_id. What is the right way to do this?

ORIGINAL QUESTION

I am sure that I am missing something obvious, but I can’t figure out how to indicate which record will be returned by the request with GROUP BY. By default it GROUP BYreturns the first record in the group, who can I get the last instead instead of using a subquery?

The main query returns the first record:

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

it 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 BYshouldn't work. The fact that your first example works is a disgusting MySQL function.

To make GROUP BY work, there cannot be a SELECT clause *. It should be a mixture of GROUP BY columns and “aggregate” functions, such as COUNT, SUMetc.

SELECT COUNT(*), some_column FROM... GROUP BY some_column - expected form.

SELECT * not expected.

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

No examples you posted are correct T-SQL. You cannot SELECT *use the offer GROUP BY.

GROUP BY - ​​ , ( , SELECT).

(, SUM COUNT MAX) SELECT. , , COUNT :

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

Answer 3

, , SQL, ...

:

FROM JOIN

GROUP BY ( )
HAVING
ORDER BY /

Answer 4

GROUP BYshould be used with aggregate functions, such as count, sum, avg, etc. For example:

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