Create a view with column num_rows - MySQL

Question

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.

Let's say I've this normal table:

| country | name | age | price |
--------------------------------
| US      | john | 22  | 20    |
| France  | Anne | 10  | 15    |
| Sweden  | Alex | 49  | 10    |

And so on...

The view I want to create is:

    | country | name | price | row_num |
    ------------------------------------
    | US      | john |  20   |    1    |
    | France  | Anne |  10   |    2    |
    | Sweden  | Alex |  5    |    3    |

And so on...

I can generate the row_num with a single select:

SELECT @i:=@i+1 AS row_num, testing.country, testing.name, testing.price
FROM testing testing,(SELECT @i:=0) derivedTable
order by name

But my problem is to combine the query above with the query creating the view. This is the combined query I'm trying:

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;

I get the following error: #1351 - View's SELECT contains a variable or parameter

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.

I hope I made myself clear otherwise I'm more than happy to explain myself in further detail.

Solution 1

I found a solution for this:

First create a function:

delimiter //

CREATE FUNCTION `func_inc_var_session`() RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
      SET @var := @var + 1;
      return @var;
     end
     //

delimiter ;

Then set @var to the number you want to start with. In this case zero.

SET @var=0;

Then create the view as following:

CREATE OR REPLACE VIEW myview (place, name, hour, price, counter) 
AS SELECT place, name, hour, price, func_inc_var_session() 
FROM yourtable
WHERE input_conditions_here;

The trick here is that you may see NULL on the counter column. If this happens please set @var again to your number and then do the SELECT * again and you'll see the counter column properly populated.

Solution 2

I tried the example of the func_inc_var_session function.

There was a small problem of session variable initialization that I solved using the mysql IFNULL function.

Below the enhanced func_inc_var_session function.

CREATE DEFINER=`root`@`localhost` FUNCTION `func_inc_var_session`() RETURNS int(11)
begin
  SET @var := IFNULL(@var,0) + 1;
  return @var;
end

Solution 3

When using the solution from @dazito you might encounter an issue with the counter continually incrementing from query to query, for example when your application reuses a session, like with JPA / Hibernate. For example:

Query 1:

| country | name | price | row_num |
------------------------------------
| US      | john |  20   |    1    |
| France  | Anne |  10   |    2    |
| Sweden  | Alex |  5    |    3    |

Query 2:

| country | name | price | row_num |
------------------------------------
| US      | john |  20   |    4    |
| France  | Anne |  10   |    5    |
| Sweden  | Alex |  5    |    6    |

etc.

One solution to this is to join the main query with a (one-time) call to the counter function and parameterize the function (the 'reset' parameter below) to let it know it is the first call.

delimiter //
CREATE FUNCTION `func_inc_var_session`(reset BIT) RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
      IF reset THEN
        SET @var := 0;
      ELSE
        SET @var := IFNULL(@var,0) + 1;
      END IF;
      return @var;
     end
     //
delimiter ;

Now you can call the function in your view query with the reset parameter set to 1 to set the function's counter variable back to 0, and with 0 to increment the counter. The function will only get called once with 1 as a parameter when joining with it as below:

CREATE OR REPLACE VIEW country_view (country, name, price, row_num) 
AS SELECT country, name, price, func_inc_var_session(0) 
FROM country
JOIN (SELECT func_inc_var_session(1)) r

Now you are guaranteed row number 1, 2, 3 every time.

Solution 4

Or try this-> create a temporary table and insert your data into it like bellow

CREATE OR REPLACE TEMPORARY TABLE myview (
    country VARCHAR(250), 
    name VARCHA(50), 
    price VARCHAR(50), 
    row_num int(11)
);

SET @row_num = 0;
INSERT INTO myview (country,name,price,row_num)
    SELECT @row_num:=@row_num+1 
        as country,name,price,row_num 
    FROM testing;

SELECT * FROM myview;

+---------+------+------+-------+---------+
| country | name | age  | price | row_num |
+---------+------+------+-------+---------+
| Sweden  | Alex |   49 |    10 |       1 |
| France  | Anne |   10 |    15 |       2 |
| France  | Anne |   11 |    16 |       3 |
| US      | john |   22 |    20 |       4 |
+---------+------+------+-------+---------+

View more solutions

Comments

[dazito]

That's not possible I'm afraid because it's using a variable on the create view, I get the same error as before: Error Code: 1351. View's SELECT contains a variable or parameter 0.000 sec

[dazito]

On your 5th line, where you have ON t2.name < t1.name OR (t2.name = t1.name AND t2.id <= t1.id) what exactly is t1.id and t2.id? I'm asking because I've no id column and I'm not sure to what that would refer to in my case.

[Devart]

Oh, sorry. I meant unique ID column. I will add complete example.

[Blackbam]

Welcome to SO. Formatting code in your answers properly is very helpful :-)

[SlimDeluxe]

Please note the caveat: if you use the function twice or more times in a single query (e.g. with JOIN), it will continue to increase the variable.

[user43326]

OP wants to get the result as a view, and your answer won't work as view.

[user43326]

When I do this, with "order by " within "input_conditions_here", I get func_inc_var_session() in wrong order. I think one needs a subquery here.

[Surya Kameswara Rao Ravi]

MySQL does not allow Sub-Queries in a VIEW. The select will work independently but the VIEW will NOT be created. Error: #1349 - View's SELECT contains a subquery in the FROM clause.

[Surya Kameswara Rao Ravi]

The sub query limitation in a VIEW is for MySQL 5.7.7 and below versions.