How to limit and show results based on categories?

Question

I am trying to figure out how to display a certain number of products per category and filter by number of sales. I can't seem to figure it out.

Lets say I have 100 products in my database "mysql" that are in 5 different categories and I want to display 10 results per page but show 2 results per category per page and filter by number_of_sales highest to lowest. What is the best way to go about this?

I have a test table created already and I have 6 columns in it which are id, product_id, product_name, category, category_id, number_of_sales

id, product_id,  product_name,  category,  category_id, number_of_sales
1  | 1          | product1    | category1 | 1          | 5
2  | 2          | product2    | category1 | 1          | 10
3  | 3          | product3    | category1 | 1          | 15
4  | 4          | product4    | category1 | 1          | 4
5  | 5          | product5    | category1 | 1          | 6
6  | 6          | product6    | category2 | 2          | 14
7  | 7          | product7    | category2 | 2          | 6
8  | 8          | product8    | category2 | 2          | 1
9  | 9          | product9    | category2 | 2          | 0
10 | 10         | product10   | category2 | 2          | 2
11 | 11         | product11   | category3 | 3          | 17
12 | 12         | product12   | category3 | 3          | 2
13 | 13         | product13   | category3 | 3          | 6
14 | 14         | product14   | category3 | 3          | 4
15 | 15         | product15   | category3 | 3          | 7
16 | 16         | product16   | category4 | 4          | 3
17 | 17         | product17   | category4 | 4          | 8
18 | 18         | product18   | category4 | 4          | 7
19 | 19         | product19   | category4 | 4          | 1
20 | 20         | product20   | category4 | 4          | 0 
21 | 21         | product21   | category5 | 5          | 6
22 | 22         | product22   | category5 | 5          | 4
23 | 23         | product23   | category5 | 5          | 7
24 | 24         | product24   | category5 | 5          | 8
25 | 25         | product25   | category5 | 5          | 5

I have tried many different queries in a while loop and have not been able to achieve the correct output. The best I was able to get was getting 5 results to show, 1 from each category of either highest or lowest sales using group by and order by.

EDIT:

Output sample. Something kind of like this.

1   product11   | category3 | 3          | 17
2   product3    | category1 | 1          | 15
3   product6    | category2 | 2          | 14
4   product2    | category1 | 1          | 10
5   product17   | category4 | 4          | 8
6   product15   | category3 | 3          | 7
7   product18   | category4 | 4          | 7
8   product23   | category5 | 5          | 7
9   product7    | category2 | 2          | 6
10  product21   | category5 | 5          | 6

It will be more clear if you post the expected output sample like your sample table. — Karan Desai, Oct 29 '17 at 03:35
https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Tim Biegeleisen, Oct 29 '17 at 03:42
I seen that page and a few others alreadyand I was hoiping to not have to use a bunch of unions. I already tried that and didn't get what I was looking for but I didn't mess with it much but I will try again. — Richard, Oct 29 '17 at 03:45

score 1 · Accepted Answer · answered Oct 29 '17 at 03:47

What about this sqlfiddle

select product_name, `category`, sales
from 
(
   select product_name, `category`, sales,
      (@num:=if(@category = `category`, @num +1, if(@category := `category`, 1, 1))) row_number 
  from my_table t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `category`, sales desc, product_name
) as x 
where x.row_number <= 2 order by sales desc;

It's referenced from this link

You can add after "sales desc" LIMIT 0, 10 where 0 is your offset, offset = (page-1)*10

How to limit and show results based on categories?

1 Answers1