There is a model "Channel"

+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+

There is a model "Mix"

 +---------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(255) | YES | | NULL | | | channel_id | int(11) | YES | MUL | NULL | | | on_channel | tinyint(1) | NO | | 0 | | +---------------------+--------------+------+-----+---------+----------------+

Each mix belongs to a channel.

Request to output all mixes from channel 1, 5 и 15 :

 "SELECT `mixes`.* FROM `mixes` WHERE `mixes`.`on_channel` = 1 AND `mixes`.`channel_id` IN (1, 5, 15)" +------+-------------------------------------+------------+ | id | title | channel_id | +------+-------------------------------------+------------+ | 879 | It's Disco time! MILO 8 | 5 | | 882 | ElectroDiscoFunkEklektika | 1 | | 891 | Pleasure Pizza | 5 | | 953 | Elite Disco Funk | 15 | | 958 | Special mix for Cottonmouth Podcast | 5 | | 967 | Disco on the way | 1 | | 998 | okoloDISKO #9 for FLAT.FM | 15 | | 1119 | Some Funk For Your Ass | 5 | | 1267 | GET PHONKEY WITH YOU | 15 | | 1272 | Friendly Vibes Mix | 5 | +------+-------------------------------------+------------+

Question: how to make the sorting be in order channel_id = (1, 5, 15) iteratively, i.e.

 +------+-------------------------------------+------------+ | id | title | channel_id | +------+-------------------------------------+------------+ | 882 | ElectroDiscoFunkEklektika | 1 | | 879 | It's Disco time! MILO 8 | 5 | | 953 | Elite Disco Funk | 15 | | 967 | Disco on the way | 1 | | 891 | Pleasure Pizza | 5 | | 998 | okoloDISKO #9 for FLAT.FM | 15 | | 958 | Special mix for Cottonmouth Podcast | 1 | | 1119 | Some Funk For Your Ass | 5 | | 1267 | GET PHONKEY WITH YOU | 15 | | 1272 | Friendly Vibes Mix | 1 | +------+-------------------------------------+------------+
  • one
    you must first sort by channel, number them within the channel with sequence numbers 1,2,3, and then sort by these numbers and channel number. In version 8 of mysql, you can number using row_number (). In earlier versions using variables, as in a subquery in this ru.stackoverflow.com/a/600200/194569 answer - Mike
  • @Mike 1. "SELECT mixes .* FROM mixes` WHERE mixes . on_channel = 1 AND mixes . channel_id IN (1, 5, 15) Problem, I couldn’t build request 3 by example. sort by these serial numbers and channel number - similar to paragraph 2 If possible, help with my example - Deepsystm
  • Those. version of mysql <8? - Mike
  • @Mike 5.5.9 version - Deepsystm
  • Well, at 8 very simple row_number() over(partition by channel_id order by id) will give you numbers 1,2,3 within one channel and if you sort by it, you will already get all 1st records (of all channels), then all 2nd etc. - Mike

1 answer 1

For MySQL 5.x:

 SELECT `channel`, `title` FROM ( SELECT @row_number:=CASE WHEN @channel_id = `channel_id` THEN @row_number + 1 ELSE 1 END AS num, @channel_id:=`channel_id` as `channel`, `title` FROM `mixes` WHERE `on_channel` = 1 AND `channel_id` IN (1, 5, 15) ORDER BY `channel_id` ) `q` ORDER BY `num`, `channel`

SQLFiddle example