Mysql: getting first x records of group by

Question

I have a table with products from shops. These products have a valid_from and valid_to date. In my query, I want to have only the first x records of each shop which are currently valid, ordered by last insert desc.

I am currently using the following query:

SELECT * FROM 
    (
        SELECT 
            s.id as shopid, ss.name as shopname, p.name as productname, p.validfrom, p.validto
        FROM 
            product p 
        JOIN 
            shop s ON p.shopid = s.id 
        WHERE 
            s.status = 'Active' AND 
            (now() BETWEEN p.validfrom and p.validto) 
        ORDER BY p.insert DESC
    ) as a 

GROUP BY 
    shopid 
ORDER BY 
    shopname asc

This obviously only gives me the latest record of each shop, but I want to have latest 2 or 3 records. How can I achieve this?

Bonus question: I want to differ per shop. So for shop A I'd like to have only the first record and for shop B the first two records. You may assume I have some database field for each shop that holds this number, like s.num_of_records.

The similar issue (possible duplicate) got me in the right direction, but it not completely solve my problem (see latest comment)

Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — e4c5, Sep 23 '16 at 14:07
Not exactly the same, but a similar question that got me in the right direction, thanks. I am now using: `code` @currcount := IF(@currvalue = s.id, @currcount + 1, 1) AS rank @currvalue := s.id AS lastshopid `code` To make ranks for the same shop, however this does not always work somehow. Some shops have the right ranking, others have all 1 being the ranking. Any clue what could be wrong? I am ordering by shopid. — MNL, Sep 23 '16 at 15:21

score 0 · Answer 1 · answered Sep 23 '16 at 19:44

It works by giving each record a rank based on the previous shopid. If the shopid is the same, I rank it +1. Otherwise I rank it 1.

There still is a problem however. Even though I'm using order by shopid, it is not ranking correctly for all the shops. I added the lastshopid in the query to check and although the records are ordered by shop in the result, the lastshopid sometimes has another id. I think it must be because the ordering is done at the end instead of the beginning.

Anyone has an idea how to solve this? I need the shops in the right order to get this rank solution working.

score 0 · Answer 2 · answered Sep 23 '16 at 21:39

You can use an additional LEFT JOIN with the product table to count the number of products from the same shop that have been inserted later. That number can be compared with your num_of_records column.

SELECT s.id as shopid, s.name as shopname,
       p.name as productname, p.validfrom, p.validto

FROM shop s

JOIN product p 
  ON  p.shopid = s.id 

LEFT JOIN product p1
  ON  p1.shop_id    = p.shop_id
  AND p1.validfrom <= NOW()
  AND p1.validto   >= NOW()
  AND p1.`insert`  >  p.`insert`

WHERE s.status = 'Active'
  AND p.validfrom  <= NOW()
  AND p.validto    >= NOW()

GROUP BY p.id

HAVING COUNT(p1.id) + 1 <= s.num_of_records

ORDER BY shopname asc

Indexes that might help: shop(status, name), product(shop_id, validfrom) or product(shop_id, validto) (probably the second one).

Note 1: If you have inserted two products at the same time (same second) for the same shop and both of them are candidates to be the last in the limited list for that shop, they will be selected both. That will not happen, if you use the AUTO_INCREMENT column insted of the insert column.

Note 2: Depending on the group size (number of products per shop) this query can be slow.

That is working great, thanks a lot! Performance is not really a big issue, since I'm caching the result for some time. — MNL, Sep 24 '16 at 16:19

Mysql: getting first x records of group by

2 Answers2