CODEWITHSUNDEEP
mysql
Eric
Eric

Reputation: 10636

MySQL LIMIT and GROUP BY with use of IN?

I have a table ITEMS, which has the props id, site_id, frequency

So far i can list the items and order them by frequency from an array of id, using

SELECT * 
FROM items 
WHERE id IN(1549, 1550, 1635, 1637) 
ORDER BY site_id, frequency DESC

But i'd like to group my results by site_id with only one item that has the best (highest) frequency..

how do i do ?

Edit : I'd like to have the items that have the best frequency grouped by site_id (one result by site_id)

Upvotes: 1

Views: 235

Answers (2)

Taryn
Taryn

Reputation: 247680

Without seeing the datatypes or sample data it sounds like you want this:

select id, site_id, max(frequency) FROM items WHERE id IN(1549, 1550, 1635, 1637) GROUP BY id, site_id ORDER BY site_id, frequency DESC

Or you can use a sub-query:

select i1.id, i1.site_id, i1.frequency
from items i1
inner join
(
   select site_id, max(frequency) mx
   from items
   WHERE id IN(1549, 1550, 1635, 1637) 
   group by site_id
) i2
   on i1.site_id = i2.site_id
   and i1.frequency = i2.mx
WHERE i1.id IN(1549, 1550, 1635, 1637)

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

select i.*
from (
    select site_id, max(frequency) as MaxFrequency
    from item
    where id in (1549, 1550, 1635, 1637)    
    group by site_id
) im
inner join items i on im.site_id = i.site_id and im.MaxFrequency = i.frequency
where i.id in (1549, 1550, 1635, 1637)

Note: This will give you more than one row per site_id if both have the same frequency.

Upvotes: 2

Related Questions