0

I am trying to create a view containing a variable, in order to have a column with a unique index such it is suggested in the most voted answer of the question: ROW_NUMBER() in MySQL

The solution works in a simple select, but When I try to convert it in a view executing below sentence then I get the MySQL error.

CREATE VIEW `new_view` AS
SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

My view contain several joins but data displayed can be resumen in below registers, as can be seen some registers are repeated which is correct:

 col1   col2   col3  
 '23', 'bla', 'bla'  
 '23', 'bla', 'bla'
 '67', 'fgh', 'qwe'
 '67', 'we4', 'cdf'
 '70', 'nhn', 'yui'

What I want to get is: 

 rank col1   col2   col3  
 1    '23', 'bla', 'bla'  
 2    '23', 'bla', 'bla'
 3    '67', 'fgh', 'qwe'
 4    '67', 'we4', 'cdf'
 5    '70', 'nhn', 'yui'

As a previous step to get below result:

 rank2 col1   col2   col3  
 1    '23', 'bla', 'bla'  
 2    '23', 'bla', 'bla'
 1    '67', 'fgh', 'qwe'
 2    '67', 'we4', 'cdf'
 1    '70', 'nhn', 'yui'

What is the best way to create that index column in a view if MySQL does not allow variable usage inside views?

Gerard TV
  • 1
  • 1
  • 3
  • You can't base a view on that query. Also, side note, the rank you define is meaningless without an `ORDER BY` clause. Your workaround would be to generate rank using another method. – Tim Biegeleisen Mar 28 '18 at 08:16
  • Can you suggest any other method? In the view where I am trying to add that rank/index column there are repeated registers – Gerard TV Mar 28 '18 at 08:29
  • Edit your question and include data and your actual query, or better yet a minimal version of it. There are many ways to generate a row number, but only if your data permits it. – Tim Biegeleisen Mar 28 '18 at 08:30
  • I have edited the question to better explain my problem. – Gerard TV Mar 28 '18 at 08:49
  • I don't know of a way to do this without session variables, because you have duplicate rows. You could try selecting your current query into a temp table. – Tim Biegeleisen Mar 28 '18 at 08:56
  • Thanks for your time, I will try to first to include a column to differ all registers so I will be able to use the other suggested workarounds for ROW_NUMBER in MySQL – Gerard TV Mar 28 '18 at 09:01

0 Answers0