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.
Adwait ChitaleyAsked:
Who is Participating?
 
profyaConnect With a Mentor Commented:
Try this:
PREPARE stmt FROM "SELECT @rownum:=@rownum+1 as rownum, employee_name from (SELECT @rownum:=0) r, employees";
EXECUTE stmt;

For more information:
http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html
0
 
Steve BinkCommented:
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.
0
 
virmaiorCommented:
you can create the "view" as a table instead of a view and get the #s.
elsewhise, you can't use variables
0
 
SharathData EngineerCommented:
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;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.