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.

Why is there no auto-increment like a "normal table" that you can use? — Naftali, Apr 09 '13 at 01:53
Because I didn't design that table and that table has already thousands and thousands of records stored. Plus they don't allow me to change anything on that table, that's why I'm using views. — dazito, Apr 09 '13 at 01:58
Ah, and one more detail @Neal that I forgot to mention is the view is created with a where clause, I didn't put it there as I thought it wasn't an important detail. — dazito, Apr 09 '13 at 02:21
@dwnz There is a way to do it, but the column(s) you need to order by records should be unique. — Devart, Apr 09 '13 at 06:32
@Devart can you please show me how to do it? And I'll try to adapt it to my problem. Thank you. — dazito, Apr 09 '13 at 15:06

score 7 · Accepted Answer · answered May 02 '13 at 18:06

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.

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. — user43326, Jan 14 '18 at 17:18

score 5 · Answer 2 · edited Feb 04 '15 at 17:50

5

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

edited Feb 04 '15 at 17:50

Tim Lewis

27,813
13
73
102

answered Feb 04 '15 at 17:43

Mounir NAJAHI

51
1
1

2

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. – SlimDeluxe Sep 07 '17 at 11:57

score 2 · Answer 3 · answered Dec 19 '17 at 13:06

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.

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, Aug 17 '19 at 13:57
The sub query limitation in a VIEW is for MySQL 5.7.7 and below versions. — Surya Kameswara Rao Ravi, Aug 17 '19 at 14:22

score 1 · Answer 4 · answered Oct 09 '14 at 20:07

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 |
+---------+------+------+-------+---------+

Devart · Answer 5 · 2013-04-10T14:08:04.247

Try to apply this query to your view -

CREATE TABLE testing (
  id int(11) NOT NULL DEFAULT 0,
  country varchar(255) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  price int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO testing(id, country, name, age, price) VALUES
  (1, 'US', 'john', 22, 20),
  (2, 'France', 'Anne', 10, 15),
  (3, 'Sweden', 'Alex', 49, 10),
  (4, 'France', 'Anne', 11, 16);

SELECT
  t1.*, COUNT(*) row_num
FROM testing t1
  LEFT JOIN testing t2
    ON t2.name < t1.name OR (t2.name = t1.name AND t2.id <= t1.id)
 GROUP  BY t1.name, t1.id;

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

This query does not use any user variables, so it wotks in views.
Additional condition in ON clause helps to implement duplicated values in name field.

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. — dazito, Apr 10 '13 at 13:49
Oh, sorry. I meant unique ID column. I will add complete example. — Devart, Apr 10 '13 at 14:05

score 0 · Answer 6 · answered Sep 07 '21 at 21:41

MySql since version 8.0 supports the function ROW_NUMBER() which allows you to create a view. The problem with the old way (which is an emulation of the desired behavior) is that at the moment of creating the view, the @ in (@row_number:=@row_number + 1) confused the syntax of the view creation and throw the #1351 error.

CREATE OR REPLACE view vwx (country, name, price, num_row) AS 
    SELECT 
        country, 
        name,    
        price,
        ROW_NUMBER() OVER (ORDER BY final_score) row_num
    FROM 
        testing
    ORDER BY 
        name

score -1 · Answer 7 · edited Apr 10 '13 at 08:23

-1

CREATE OR REPLACE view vwx (country, name, price, num_row) AS
SELECT country, name, price, @index := @index + 1 AS num_row
FROM testing, (SELECT @index := 0) temp

edited Apr 10 '13 at 08:23

answered Apr 10 '13 at 08:12

ER144

690
4
10

4

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 Apr 10 '13 at 13:28

score -2 · Answer 8 · edited Feb 15 '20 at 18:26

-2

Adding Row number to a query result - No View needed - One Query

SELECT country, name, price, @ID := @ID + 1 AS row_num
FROM testing,
     (SELECT @ID := 0) temp

Add the following to your MySQL connectionString: Allow User Variables=True;

Don't add "Cross Join" in your query to update your @ID variable.

Caution: attempting to use this in a View results in: View's SELECT contains a variable or parameter

edited Feb 15 '20 at 18:26

WEBjuju

5,797
4
27
36

answered Mar 07 '17 at 19:23

Patrick Sturge

15
1

Welcome to SO. Formatting code in your answers properly is very helpful :-) – Blackbam Mar 07 '17 at 20:42
3

OP wants to get the result as a view, and your answer won't work as view. – user43326 Jan 14 '18 at 17:15

Create a view with column num_rows - MySQL

8 Answers8

Linked