Danish Bin Sofwan Danish Bin Sofwan - 9 months ago 62
SQL Question

MySql : Initialize mySql variable inside a query

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.

Answer Source

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