I want to create a view in mysql.But that in mysql does't support subquery. How to write the sql without subquery?
select * from dev_location t1
inner join
(
select
`dev_location`.`device_id` AS `device_id`,
max(`dev_location`.`id`) AS `id`
from
`dev_location`
group by `dev_location`.`device_id`) t2
on t1.id = t2.id
MySQL views don't support subqueries in the from clause. The following should work in a view:
select dl.*
from dev_location dl
where not exists (select 1
from dev_location dl2
where dl2.device_id = dl.device_id and
dl2.id > dl.id
);
This reformulates the query to say: "Get me all the rows from dev_location where the device_id has no greater id." This is an awkward way of getting the max.
And, with an index on dev_location(device_id, id), it might perform better than your version.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments