Dear Members! I use the subselects many times in my Queries to get some info (Name, etc) from a subtable.
Sometimes I have to use it to get the last element. select t.*, ( select value from u join ... where ... order by id desc limit 1 ) as last_value, It is ok, but how can I get more values from subselect without repeating the subquery? select t.*, ( select value from u join ... where ... order by date desc limit 1 ) as last_value, ( select type from u join ... where ... order by date desc limit 1 ) as last_type, This is not too comfortable, and may make mistakes if the join is not defined properly or the date has duplicates. Ok, I can use WITH Query: with pre as ( select * from t .... ) ,sub as (select pre.*, (select u.id from u where ... limit 1) as last_u_id select sub.*, u.value, u.type, u.nnn from sub left join u on (u.id = sub.last_u_id) But sometimes it makes the Query very long (because I have to read more subselects). Do you know a simple method for this, like: select t.*, ( select value, type, anyfield from u join ... where ... order by date desc limit 1 ) as last_value, last_type, anyfield ? Thank you for the help! Best regards Chris
