MySQL group data with 2 results per group and order

Question

I'm having a database table with data show below. I want to select data from this table with the following conditions.

Select all records where product_id is 1. Then get two records of each group_id where manual 1 is selected above manual 0. Then order by score DESC.

This should give me the following result:

id | product_id | group_id | manual | score
-------------------------------------------
 6 |          1 |        4 |      1 |   400
 1 |          1 |        7 |      0 |  1000
 2 |          1 |        7 |      0 |   900
 5 |          1 |        4 |      0 |   600

Is this possible in just one (simple) query?

CREATE and INSERT statements;

CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `manual` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table` (`id`, `product_id`, `group_id`, `manual`, `score`)
VALUES
    (1, 1, 7, 0, 1000),
    (2, 1, 7, 0, 900),
    (3, 1, 7, 0, 800),
    (4, 1, 7, 0, 700),
    (5, 1, 4, 0, 600),
    (6, 1, 4, 1, 400),
    (7, 1, 4, 0, 300);

Try one of the methods in the duplicate link, and if you get stuck, come back and edit your question with a query. — Tim Biegeleisen, Sep 14 '17 at 11:12
Here's that link: https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Strawberry, Sep 14 '17 at 11:21

Strawberry · Accepted Answer · 2017-09-14T11:27:21.710

1

Don't call a table table. It's just annoying. Anyway...

    SELECT id
         , product_id
         , group_id
         , manual
         , score
      FROM 
         ( SELECT x.*
                , CASE WHEN @prev = group_id THEN @i := @i+1 ELSE @i := 1 END i
                , @prev := group_id 
             FROM `table` x
                , (SELECT @prev:=null,@i:=0) vars 
            ORDER 
               BY group_id DESC
                , manual <> 1
                , score DESC
         ) n
     WHERE i <= 2
     ORDER 
        BY group_id DESC
         , manual <> 1
         , score DESC;

edited Sep 14 '17 at 11:27

answered Sep 14 '17 at 11:18

Strawberry

33,750
13
40
57

I agree that calling it `table` is not that convenient, will never do that again '). This query will do the trick thanks. I made a small mistake in my wanted result. Going to change that, changing `manual` and `group_id` in the last `order by` fixes that. – Timo002 Sep 14 '17 at 11:33

MySQL group data with 2 results per group and order

1 Answers1