title: Create a view with column num_rows - MySQL
body: <p>I need to create a view that has a column named row_num where it will be inserted the row number, just like an auto increment in a normal table.</p>
  
  <p>Let's say I've this normal table:</p>
  
  <pre><code>| country | name | age | price |
  --------------------------------
  | US      | john | 22  | 20    |
  | France  | Anne | 10  | 15    |
  | Sweden  | Alex | 49  | 10    |
  </code></pre>
  
  <p>And so on...</p>
  
  <p>The view I want to create is:</p>
  
  <pre><code>    | country | name | price | row_num |
      ------------------------------------
      | US      | john |  20   |    1    |
      | France  | Anne |  10   |    2    |
      | Sweden  | Alex |  5    |    3    |
  </code></pre>
  
  <p>And so on...</p>
  
  <p>I can generate the row_num with a single select:</p>
  
  <pre><code>SELECT @i:=@i+1 AS row_num, testing.country, testing.name, testing.price
  FROM testing testing,(SELECT @i:=0) derivedTable
  order by name
  </code></pre>
  
  <p>But my problem is to combine the query above with the query creating the view.
  This is the combined query I'm trying:</p>
  
  <pre><code>CREATE OR REPLACE view vwx (country, name, price, num_row) AS SELECT mytable.country, mytable.name, mytable.price, @i:=@i+1 AS row_number
      FROM testing testing,(SELECT @i:=0) derivedTable
      order by name;
  </code></pre>
  
  <p>I get the following error: #1351 - View's SELECT contains a variable or parameter</p>
  
  <p>I know I can't use a select inside a select with views but I don't see other way to do this view the way I want, but I'm sure there is a way to do this but I just don't know how. Probably with functions or procedures but I'm really new to them so I'm not comfortable with creating functions or procedures in mysql.</p>
  
  <p>I hope I made myself clear otherwise I'm more than happy to explain myself in further detail.</p>