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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With