title: Setting variables inside a SELECT
body: <p>I'm in the process of rewriting a <code>mySQL 5.7</code> query to <code>Snowflake</code>. I would like to keep as much of the <code>mySQL</code> query as close as possible when converting to <code>Snowflake</code>.</p>
  <p><strong>PROBLEM</strong>: The issue that I'm facing is that I have some user defined variables that change dynamically based on the previous value in a given column and I'm unable to replicate that functionality. I've been able to <code>set</code> variables but when I attempt to change their value as described I'm presented with a series of error.</p>
  <p><strong>MySQL Query</strong>:</p>
  <pre><code>SELECT
      temp.id,
      temp.profile_id,
      temp.citations,
      temp.DataValue,
      @rank := CASE
          WHEN @prevId = temp.profile_id AND @prevDataValue = temp.DataValue THEN @rank+1
          ELSE 1
      END as DataValueRank,
      @prevId := temp.profile_id AS prevId,
      @prevDataValue := temp.DataValue AS prevDataValue
  FROM
      l.temp AS temp,
      (SELECT @prevId := 0, @prevDataValue := 0, @rank := 0) AS X
  ORDER BY
      profile_id DESC,
      DataValue ASC,
      citations DESC
  </code></pre>
  <p>in the above query we have that @prevId will be the value of the previous value found in the <code>profile_id</code> column,  similarly for <code>@prevDataValue</code> and <code>DataValue</code>.</p>
  <p>The table that gets created looks like the following:</p>
  <pre><code>id          profile_id     citations   DataValue           DataValueRank   prevId       prevDataValue
  ...
  24508771    1003077033      1           E04.936.580.225     49             1003077033   E04.936.580.225
  24160975    1003077033      1           E04.987             50             1003077033   E04.987
  24160975    1003077033      1           E04.987.775         51             1003077033   E04.987.775
  28079605    1003077025      9           C10                  1             1003077025   C10
  28079605    1003077025      9           C10.597              2             1003077025   C10.597
  ...
  </code></pre>
  <p>Where <code>l.temp</code> is the same as above with the last three columns:</p>
  <pre><code>id          profile_id     citations    DataValue           
  ...
  24508771    1003077033      1           E04.936.580.225     
  24160975    1003077033      1           E04.987             
  24160975    1003077033      1           E04.987.775         
  28079605    1003077025      9           C10
  28079605    1003077025      9           C10.597
  ...
  </code></pre>
  <p><strong>ATTEMPT</strong></p>
  <p><strong>Snowflake Query</strong></p>
  <pre><code>SET (prevId, prevDataValue, rank) = (0, 0, 0); -- AS X 
  </code></pre>
  <p>I'm thinking that this acts as my variable initializer similar to</p>
  <pre><code>SELECT @prevId := 0, @prevDataValue := 0, @rank := 0) AS X
  
  SELECT
      temp.id,
      temp.profile_id,
      temp.citations,
      temp.DataValue,
  --    $rank = CASE
  --        WHEN $prevId = temp.profile_id AND $prevDataValue = temp.DataValue THEN $rank+1
  --        ELSE 1
  --    END as DataValueRank, -- THIS WON'T CHANGE VALUE DEPENDING ON THE CASE
  --    $prevId = temp.profile_id, -- THIS WON'T CHANGE VALUE
  --    $prevDataValue = temp.DataValue -- THIS WON'T CHANGE VALUE
      FROM
          DATAWAREHOUSE.MY_DATA AS temp,
      ORDER BY
          profile_id DESC,
          DataValue ASC,
          citations DESC
  ;
  </code></pre>
  <p>If anybody knows how the variables can change value within the <code>SELECT</code> statement that would be helpful</p>