Danish Bin Sofwan Danish Bin Sofwan - 1 month ago 16
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

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
Comments