title: Define and use a variable with a subquery?
body: <p>I know normally <a href="//dev.mysql.com/doc/refman/5.5/en/user-variables.html" rel="nofollow">"the order of evaluation for expressions involving user variables is undefined"</a> so we can't safely define and use a variable in the same <code>select</code> statement. But what if there's a subquery? As an example, I have something like this:</p>
  
  <pre><code>select col1,
         (select min(date_)from t where i.col1=col1) as first_date,
         datediff(date_, (select min(date_)from t where i.col1=col1)
                 ) as days_since_first_date,
         count(*) cnt
  from t i
  where anothercol in ('long','list','of','values')
  group by col1,days_since_first_date;
  </code></pre>
  
  <p>Is there a way to use <code>(select <b>@foo:=</b>min(date_)from t where i.col1=col1)</code> safely instead of repeating the subquery? If so, could I do it in the <code>datediff</code> function or the first time the subquery appears (or either one)?</p>
  
  <hr>
  
  <p>Of course, I could do</p>
  
  <pre><code>select col1,
         (select min(date_)from t where i.col1=col1) as first_date,
         date_,
         count(*) cnt
  from t i
  where anothercol in ('long','list','of','values')
  group by col1,date_;
  </code></pre>
  
  <p>and then do some simple postprocessing to get the <code>datediff</code>. Or I can write two separate queries. But those don't answer my question, which is whether one can safely define and use the same variable in a query and a subquery.</p>