SQL statement that organizes a series of items, one per set

Question

I would like to write a sql statement that lists a series of products that all belong to sets to be ordered one per set, and list through all of the products.

My products are organized like this.

Set 01
Product 01
Product 02
Product 03

Set 02
Product 06
Product 07
Product 08
Product 09

Set 03
Product 11
Product 12
Product 13
Product 14
Product 15

I’d like to create a sql query that organizes them like this

Set 01 - Product 01
Set 02 - Product 06
Set 03 - Product 11
Set 01 - Product 02
Set 02 - Product 07
Set 03 - Product 12
Set 01 - Product 03
Set 02 - Product 08
Set 03 - Product 13
Set 02 - Product 09
Set 03 - Product 14
Set 03 - Product 15

I can get a query of products grouped by set, but am unable to get one per set.

select 
       `product_set`.`product_id`, 
       `product_set`.`set_id`,
       `product_set`.`order` 
from 
       `product_set` 
inner join 
       `sets` 
on  
       `product_set`.`set_id` = `sets`.`id` 
where 
       `condition` = true

if this was `sql-server` done deal... – RoMEoMusTDiE May 18 '18 at 00:05 — RoMEoMusTDiE, May 18 '18 at 00:05
@mastashufu seems pretty straightforward to me – Strawberry May 18 '18 at 00:20 — Strawberry, May 18 '18 at 00:20

score 2 · Answer 1 · answered May 18 '18 at 00:24

2

Take a look at this answer: https://stackoverflow.com/a/532941/4770578

You need to do something like:

SELECT * FROM (
    SELECT product_id, set_id,
        @product:=CASE WHEN @set <> set_id THEN 0 ELSE @product+1 END AS rn,
        @set:=set_id AS r_set
    FROM
        (SELECT @product:= -1) s,
        (SELECT @set:= -1) c,
        (SELECT *
            FROM product_sets
            ORDER BY set_id, product_id
        ) t
    ) t1
ORDER BY rn, set_id, product_id;

Creating row numbers for each of your rows and then sorting by those row numbers.

answered May 18 '18 at 00:24

Carson Crane

1,197
8
15

Thank you, this worked nicely. Actually the other answer worked as well but yours was slightly more efficient (7ms) and you answered first. – whoacowboy May 18 '18 at 16:06
Sorry to switch the correct answer. I was getting some funky results with the query after I investigated it further. Thank you all the same. – whoacowboy May 18 '18 at 22:00

score 2 · Accepted Answer · edited May 18 '18 at 15:07

2

Basically, you can enumerate the values by set:

select ps.*
from (select ps.*,
             (@rn := if(@s = set_id, @rn + 1,
                        if(@s := set_id, 1, 1)
                       )
             ) as rn
      from (select ps.product_id, ps.set_id, ps.order 
            from product_set ps join 
                 sets s
                 on ps.set_id = s.id
            where s.conditionk = true
            order by ps.set_id, ps.order
           ) ps cross join
           (select @s := -1, @rn := 0) params
      ) ps
order by rn, set_id;

edited May 18 '18 at 15:07

whoacowboy

6,982
6
44
78

answered May 18 '18 at 02:19

Gordon Linoff

1,242,037
58
646
786

Thank you, this worked nicely. The other answer was slightly more efficient so I marked that one correct. – whoacowboy May 18 '18 at 16:06
1

@whoacowboy . . . Unfortunately, it is *not* correct, because MySQL does not guarantee the order of evaluation of expressions. You might not even know if it weren't producing the correct results. – Gordon Linoff May 18 '18 at 21:57
I just discovered that and switched the correct answer, thank you, again. – whoacowboy May 18 '18 at 21:59

SQL statement that organizes a series of items, one per set

2 Answers2