MySql : Initialize mySql variable inside a query

mysql, sql

Solution

You don't have to initialize the variable in the subquery. You can instead initialize the variable using `CROSS JOIN`:

SELECT time_of_day, 
       @channel_rank := IF(@current_channel = channel, 1, 
                             @channel_rank + 1) AS channel_rank, 
       @current_channel := channel AS channel,Views
FROM
(
   SELECT time_of_day,channel, SUM(Views) AS 'Views'
   FROM access_logs_meaningful_optimized
   WHERE `time_of_day` = 0
   AND playing_date = '2016-10-26' 
   GROUP BY channel
   ORDER BY SUM(views) DESC
   LIMIT 5
) AS xx
CROSS JOIN (SELECT @channel_rank := 0) var

Problem

This is the sequence of my MySql Query executions: Query 1 : `SET @channel_rank = 0;` Query 2 : ``` SELECT time_of_day, @channel_rank := IF( @current_channel = channel, 1, @channel_rank + 1 ) AS channel_rank , @current_channel := channel AS channel,Views FROM ( SELECT @channel_rank = 0,time_of_day,channel, SUM(Views) AS 'Views' FROM access_logs_meaningful_optimized WHERE `time_of_day` = 0 AND playing_date = '2016-10-26' GROUP BY channel ORDER BY SUM(views) DESC LIMIT 5 ) xx; ``` Sample result : ``` time_of_day channel_rank channel Views ----------- ------------ --------------------- -------- 0 1 Tolo 1291 0 2 Tolo News 855 0 3 Samaa News 805 0 4 Ary Digital 695 0 5 Dunya News 653 ``` Over here I have to execute `SET @channel_rank = 0;` first in order to assign the variable (`@channel_rank`) to 0. My question is HOW, inside the `query 2` can I assign the the variable (`@channel_rank`) to 0 initially making the `second query` independent of the `first` one.

Original source