Cannot Create VIew on MySQL

Question

It's Possible to Create this VIEW on MySQL? The problem is with the variables. I can't find a way around it.

CREATE VIEW vw_ranking AS 

SELECT rank.ativid_id, rank.user_id, b.nome, rank.quant

FROM
(SELECT user_id, ativid_id, quant, 

    @ativ_rank := IF(@current_ativ = ativid_id, @ativ_rank + 1, 1) AS ativ_rank,
    @current_ativ := ativid_id 

FROM (SELECT ativid_id, user_id, COUNT(user_id) as quant FROM tb_registro_ativ

    GROUP BY ativid_id, user_id) atividade
    ORDER BY ativid_id, quant DESC

) rank INNER JOIN tb_usuarios b ON rank.user_id = b.user_id

WHERE ativ_rank <= 10;

Dr. Stitch · Accepted Answer · 2016-06-03 01:01:40Z

You cannot define variables with view creation.

Source: https://dev.mysql.com/doc/refman/5.5/en/create-view.html

A view definition is subject to the following restrictions:

The SELECT statement cannot contain a subquery in the FROM clause.

The SELECT statement cannot refer to system variables or user-defined variables.

Within a stored program, the SELECT statement cannot refer to program parameters or local variables.

The SELECT statement cannot refer to prepared statement parameters.

Any table or view referred to in the definition must exist. After the view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.

I suggest you use a stored procedure instead.

Gordon Linoff · Accepted Answer · 2016-06-03 02:08:42Z

1

Views are severely limited in MySQL. You cannot use variables and you cannot use subqueries in the FROM clause.

Your query is quite complicated. You can use subqueries in the SELECT, and that often makes it possible to calculate ranks -- at least on small tables. In your case, you might need a series of views to accomplish what you want.

answered Jun 3, 2016 at 2:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Cannot Create VIew on MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related