MySQL Select top 10 items for each category

Question

Hello I have a mysql database in which has multiple categories. I would like to be able to pull only the first 10 items per category for example:

I have the following table, I would like to pull the first 2 rows for name = a, same for name = b and name = c

-----------------------------------
name | value | description | logo
-----------------------------------
a    | 2.00  | its a letter| image
-----------------------------------
a    | 5.00  | its a letter| image
-----------------------------------
b    | 6.00  | its a letter| image
-----------------------------------
c    | 3.00  | its a letter| image
-----------------------------------
c    | 1.00  | its a letter| image
------------------------------------

This is what I have so farm post filter is a string of objects that comes in when the call is made. unfortunately it only gives me the first 10 of everything together, if you can point me in the right direction that would be great thank you!

code:

SELECT * 
FROM object_list 
Where object IN (".$_POST['filter'].") 
ORDER BY date DESC, object ASC,id DESC

score 3 · Answer 1 · answered Feb 22 '17 at 23:53

3

You can get groups along with element count by the below query:

SELECT name, value, 
@count := IF(@value = name, @count + 1, 1) AS count,
@value := name AS some_value
FROM test, (SELECT @count := 1, @value := NULL) a
WHERE test.name in ('a', 'b')

Now, if you need to restrict the rows to 2 per group then you just need to wrap this query into another select and add a criteria, e.g.:

SELECT * 
FROM (
  SELECT name, value, 
  @count := IF(@value = name, @count + 1, 1) AS count,
  @value := name AS some_value
  FROM test, (SELECT @count := 1, @value := NULL) a
  WHERE test.name in ('a', 'b')) a
WHERE a.count <= 2;

Here's the SQL Fiddle.

answered Feb 22 '17 at 23:53

Darshan Mehta

30,102
11
68
102

Thank you for your comment, I tested on my end and it almost works great, I found that if the list is not in order and you have lets say 8 names with the latter a spread out on the table you will get groups of 2 a's . – paul590 Feb 23 '17 at 23:26
1

Yes, we need to add `order by` for that. However, I did not find any column and hence, didn't include. Also, there is no default sorting order for MySQL so you need to have a column which you can `order by` on. – Darshan Mehta Feb 24 '17 at 08:08
Awesome thank you! Its pulling the information however is pulling multiple groups of 2 for a and b, is there anyway to only have it pull 2 of each? – paul590 Feb 24 '17 at 23:05

miken32 · Accepted Answer · 2017-02-23T23:32:59.810

1

Does this work?

SELECT 
    yourtable.* 
FROM 
    yourtable 
    JOIN (
        SELECT 
            t1.name, 
            t1.value, 
            COUNT(t2.name) AS theCount 
        FROM yourtable t1 
        LEFT JOIN yourtable t2 ON t1.name = t2.name AND t1.value > t2.value 
        WHERE t1.name in ('a', 'b')
        GROUP BY t1.name, t1.value 
        HAVING theCount < 2
    ) AS dt USING (name, value);

Source: http://thenoyes.com/littlenoise/?p=36

edited Feb 23 '17 at 23:32

answered Feb 22 '17 at 23:53

miken32

42,008
16
111
154

thank you for your response! I tried your code but i am unsure on where can I specify I want for example names a and b? – paul590 Feb 23 '17 at 23:31
1

Give my edit a try; without a test data I can't be sure it will work. – miken32 Feb 23 '17 at 23:33
thank you again for your help, its almost there I am able to get some results unfortunately it seems its not pulling what I am looking for, it seems to be pulling from the top of the list is there any way to make it pull from the bottom? – paul590 Feb 24 '17 at 23:04
1

Change `t1.value > t2.value` to `t1.value < t2.value` – miken32 Feb 24 '17 at 23:05
1

works great! thank you for all your help! – paul590 Feb 24 '17 at 23:21

MySQL Select top 10 items for each category

2 Answers2