CODEWITHSUNDEEP
mysqlsubqueryview
Nicoale
Nicoale

Reputation: 503

Alternative to using a subquery - can't create view in Mysql

I have created a view that pulls a range of rows based on a calculation; using this script (per this question: How to select Range of rows based on field values - MySQL :

select t.*
from curdataEvents t cross join
     (select max(revs) as maxrev from curdataEvents) x
where t.revs >= x.maxrev - 100000;

This pulls the range of rows I need. In order to get the desired report - I have to create multiple views each creating the next layer of the report. The problem is MySQL won't create a view using a subquery. Any ideas on how to re-write the script above that would yield the same results but allow me to create a view? I have tried multiple variations using a UNION clause, etc. What's tripping me up is this is a join to itself. The examples I've found so far using multiple tables. Any help is greatly appreciated!!!

Thanks

Upvotes: 0

Views: 629

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can use a subquery, just not in the FROM clause. Just move it to the WHERE clause;

CREATE VIEW view1 AS 
SELECT t.*
FROM curdataEvents t 
WHERE t.revs >= (SELECT MAX(revs) - 100000 AS maxrev FROM curdataEvents)

An SQLfiddle to test with.

Upvotes: 1

Related Questions