6

I have created a view in MySql. But now my requirement is to create an Id column in that view which should be auto increment.

My current view is:-

CREATE VIEW pending_assign_report_view AS
  select cg.group_name,c.client_name, wt.work_type,p.Minor_worktype, ay.year,aev.emp_name,ep.Date_assigned_on
from employee_project ep,active_employee_view aev, project p, client_group cg, client c,work_type wt,assessment_year ay
where ep.task_status_id=1 and ep.username=aev.username and ep.project_id=p.project_id and p.Year_id=ay.Year_id
and p.Client_group_id=cg.client_group_id and p.Client_id=c.Client_id and p.Work_type_id=wt.Work_type_id
order by cg.group_name,c.client_name, aev.emp_name;

Now I want Id column as first column which should be auto_increment in nature. How should I do this? Thanks in advance.

Yogesh Patil
  • 61
  • 1
  • 1
  • 5
  • By the way, I think this question is a duplication: http://stackoverflow.com/questions/1964811/row-rank-in-a-mysql-view/1964850 – Alberto Segura Jun 19 '13 at 08:17
  • Short answer: No. http://stackoverflow.com/questions/2808759/creating-a-mysql-view-with-an-auto-incrementing-id-column – David Gras Mar 17 '16 at 15:37
  • Duplicate of https://stackoverflow.com/questions/2808759/creating-a-mysql-view-with-an-auto-incrementing-id-column/45963661#45963661 – Veve Aug 31 '17 at 09:35

4 Answers4

5

You can simulate it with something like this in your SELECT:

SELECT @rownum:=@rownum+1 AS rownum, dummy.*
FROM (SELECT @rownum:=0) r, dummy;
Alberto Segura
  • 755
  • 1
  • 12
  • 28
  • Note: this will enumerate all rows in your resultset, indepent of the order of your actual query! – Kaii Jun 19 '13 at 08:22
  • 9
    Thanks for quick reply. But when I m creating view mysql gives error:- View's select contains a variable or parameter. – Yogesh Patil Jun 19 '13 at 08:29
2

View in database is a mirror copy of our some data from our database. In real it does not exist. It have same structure as of main table

So its structure can not change

Lokesh Kumar
  • 420
  • 3
  • 12
0

You can USE the "UIID()" function that will provide you with an unique (alphanumerical) identified.

Check this out: https://stackoverflow.com/a/45963661/6083044

letimome
  • 906
  • 2
  • 9
  • 21
  • There is one major drawback with this solution: the UUIDs change every time you access the view. (BTW, don't reply to duplicates questions with the same answer, flag/vote to close the newer questions as duplicate of the oldest one.) – Veve Aug 31 '17 at 09:57
-1

select @rownum:=@rownum+1 'autoID',m.mediaThumbnailUrl , q.surveyID from (SELECT @rownum:=0) r, media m join question q on m.mediaAutoID = q.backgroundImageID ;

veera
  • 1
  • 2