B A B A - 4 years ago 67
MySQL Question

How to make group by work by specifying a single column name in GROUP BY with ONLY_FULL_GROUP_BY enabled

Basically I am just looking for an equivalent of this sql statement:

SELECT * FROM products GROUP BY merchant_id ORDER BY id DESC LIMIT 10;

This works fine when ONLY_FULL_GROUP_BY is disabled, but when its enabled, it doesn't work. What is the equivalent sql statement for the above to make it work on the ONLY_FULL_GROUP_BY mode?

What I am trying to do is lets say I have 100 products with ids 1 to 100 that are distributed among 10 merchants. So I want to list out the latest 10 products, 1 from each merchant.

Here is a sample table and expected output. (assume merchant 1, merchant 2 etc is the secondary key column for merchnat_id) So as you can see, what i need is one product(the one with the highest primary key ie the latest for that merchant) from each unique merchant.

Answer Source

In a subquery grab the max(id) per merchant and in the outer query join this back to the products table to get the other fields:

select p.*
from products p
inner join (select merchant_id, max(id) as max_id
            from products
            group by merchant_id
            order by max(id) desc
            limit 10) t1 on p.id=t1.max_id

You should not really rely on how MySQL implements the relaxed group by clause because that may change without any notice. Remember: nothing guarantees even if ONLY_FULL_GROUP_BY sql mode is turned off, that the query in the question would produce the expected output!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download