CODEWITHSUNDEEP
mysql
James Juanjie
James Juanjie

Reputation: 219

WHERE Clause in a complex MYSQL query?

I have the following query which works fine.

But I now need to add a WHERE clause to it but I cannot figure out how and where I should place my WHERE clause.

SELECT COUNT(*) max_streak 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = ms_date - INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=ms_date  
         FROM 
            ( SELECT DISTINCT ms_date FROM TABLE_NAME ORDER BY ms_date) x
         JOIN 
            ( SELECT @prev:=null,@i:=0) vars 
     ) a  
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC

Could someone please advice on this?

EDIT:

In TABLE_NAME I have a userEmail column.

I need to run the entire query above WHERE userEmail='[email protected]'

I hope that makes sense better now.

Upvotes: 0

Views: 31

Answers (1)

KonstantinYu
KonstantinYu

Reputation: 125

Just add your WHERE as usually after FROM definition:

 SELECT COUNT(*) max_streak 
 FROM 
     ( SELECT x.*
            , CASE WHEN @prev = ms_date - INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=ms_date  
         FROM 
            ( SELECT DISTINCT ms_date FROM TABLE_NAME WHERE TABLE_NAME.userEmail='[email protected]' ORDER BY ms_date) x
         JOIN 
            ( SELECT @prev:=null,@i:=0) vars 
     ) a  
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC

But sometimes its better to pick your fileds from internal subselect to filter with WHERE outside whole final query result.

Upvotes: 1

Related Questions