[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Variable in Mysql VIEW

Posted on 2009-12-24
Medium Priority
Last Modified: 2012-08-13
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.
Question by:Adwait Chitaley
LVL 51

Expert Comment

by:Steve Bink
ID: 26121383
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.
LVL 20

Expert Comment

ID: 26151403
you can create the "view" as a table instead of a view and get the #s.
elsewhise, you can't use variables
LVL 14

Accepted Solution

profya earned 2000 total points
ID: 26152847
Try this:
PREPARE stmt FROM "SELECT @rownum:=@rownum+1 as rownum, employee_name from (SELECT @rownum:=0) r, employees";

For more information:
LVL 41

Expert Comment

ID: 26335832
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;

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question