Link to home
Start Free TrialLog in
Avatar of Adwait Chitaley
Adwait ChitaleyFlag for India

asked on

Variable in Mysql VIEW

Please peruse the following Query:

SELECT @rownum:=@rownum+1 as rownum, employee_name from (SELECT @rownum:=0) r, employees

This query is used in MySQL to derive ROWNUMBERS (as done in case of Oracle)

My query is that... how can one use this query to create a VIEW in MySQL... when MySQL returns "VARIABLE inside View" error?


Any alternate method of availing ROWNUM in MySQL Views itself?

Thanks in advance.
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Just throwing this out there...  If your view is meant to be read-only, you do it as the return of a stored procedure instead.
you can create the "view" as a table instead of a view and get the #s.
elsewhise, you can't use variables
ASKER CERTIFIED SOLUTION
Avatar of profya
profya
Flag of Sudan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try this query. You can create a view using this SQL.

select (select count(*) from employees e2 where e1.employee_name >= e2.employee_name) as rownum,employee_name
  from employees e1;