CODEWITHSUNDEEP
mysqlsqlview
jason
jason

Reputation: 3962

create view from this query

The following query

  1. Displays result with ranks perfectly but the second query

  2. Does not create a view in mysql.

How do I create a view of the query below? I have tried many ways but unable to figure it out.

1.

SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes, (SELECT @rownum := 0) t ORDER BY vote DESC

2.

declare @rownum int

CREATE VIEW V AS SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes, (SELECT @rownum := 0) t ORDER BY vote DESC

Upvotes: 1

Views: 149

Answers (1)

Wrikken
Wrikken

Reputation: 70480

You can't use user variables in a VIEW unfortunately. You'll have to create it the hard way, probably something like:

CREATE VIEW V AS 
SELECT COUNT(y.*) as rank, x.name, x.vote
FROM uservotes x 
JOIN uservotes y 
  ON y.votes >= x.votes
GROUP BY x.some_unique_column
ORDER BY x.vote DESC

Which will be quite a bit slower then your original query.

Note it will rank 2 entries with the same vote the same, if you don't want that, you need a subquery, or an extra ON clause to decide tie breaks.

Upvotes: 1

Related Questions