I'm trying to create a view on a remote mysql database and unfortunately it appears that the version installed (5.7) does not support window functions. Everything worked on my local database but now I'm a little stuck.
Here's previous code:
create or replace view my_view as
(
with a as
(select a.*, DENSE_RANK() over (partition by SHOP order by TIMESTAMP(LOAD_TIME) DESC) rn
from my_table as a)
select row_number() OVER () as id, SHOP, LOAD_TIME from a WHERE a.rn = 1
);
Mysql 5.7 doesnt support CTE either, but that isn't a big problem.
Any hints how to solve this?
Replacing the dense_rank()
is pretty easy. However, replacing the row_number()
is more difficult. MySQL does not allow variables in views. Unfortunately, that leaves you with an inefficient subquery for the row number as well:
select (select count(distinct shop)
from mytable t2
where t2.shop <= t.shop
) as id,
shop, load_time
from mytable t
where t.load_time = (select max(t2.load_time) from mytable t2 where t2.shop = t.shop);
Or, if these are the only two columns you have, use aggregation:
select (select count(distinct shop)
from mytable t2
where t2.shop <= t.shop
) as id,
shop, max(load_time) as load_time
from mytable t
group by shop;
This is not efficient. In a simple query, you could use variables:
select (@rn := @rn + 1) as id,
shop, load_time
from mytable t cross join
(select @rn := 0) params
where t.load_time = (select max(t1.load_time) from mytable t1 where t1.shop = t.shop);
If performance is an issue, then you may want to create a table rather than a view and keep it up-to-date using triggers.