1

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.

EDIT 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. SQL TABLE SAMPLE

B A
  • 1,089
  • 2
  • 14
  • 24

1 Answers1

4

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!

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • You've beaten me to it. Take a look at join condition, as field name doesn't match the alias given – ignasi Aug 26 '16 at 09:36
  • wow this doesn't look as straightforward as I had expected it to be.. I thought there was some nifty keyword that I could simply add to the statement I mentioned in the question and it would work. I will try this anyway. – B A Aug 26 '16 at 11:30
  • WOW, does exactly what i wanted. thanks. I will wait some time before marking it as the right answer, just in case anyone posts a shorter version if possible. – B A Aug 26 '16 at 11:35
  • Don't expect any shorter version, this is how it's done! – jarlh Aug 26 '16 at 11:40
  • You could use the any_value() function on the fields that are not in the group by list and are not part of the max(id), however, again, it does not guarantee that you would get the results that you are expecting (hint: the name of the function). The solution I provided will provide you with the expected results, regardless of MySQL implementation details. – Shadow Aug 26 '16 at 11:57
  • @Shadow i tried any_value it doesn't give the results from the same row, kind of mixes up the info from different rows which is not very ideal. – B A Aug 26 '16 at 15:36