Thread: Get more columns from a lookup type subselect
select value from u join ... where ...
order by id desc limit 1) as last_value,
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,
select value, type, anyfield from u join ... where ...
order by date desc limit 1) as last_value, last_type, anyfield
?
Hi Chris:
You can use a subselect in the `from` and `join` clauses. That's how I get multiple columns from a single subselect.
If the subselect needs to use a reference from the outer scope (i.e. the main query) , you can use the `lateral` modifier.
Best regards.
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:withpre as ( select * from t .... ),sub as (select pre.*, (select u.id from u where ... limit 1) as last_u_idselect sub.*, u.value, u.type, u.nnn from subleft 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 regardsChris
Can you show me the usage with some short examples?
Hi Chris:
You can use a subselect in the `from` and `join` clauses. That's how I get multiple columns from a single subselect.
If the subselect needs to use a reference from the outer scope (i.e. the main query) , you can use the `lateral` modifier.
Best regards.
On 10/03/2023 08:34, Durumdara wrote: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:withpre as ( select * from t .... ),sub as (select pre.*, (select u.id from u where ... limit 1) as last_u_idselect sub.*, u.value, u.type, u.nnn from subleft 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 regardsChris
For example, if you wanted to list book authors and the latest book of each one, I would do something like this:
SELECT
author.first_name,
author.surname,
latest_book.title,
latest_book.release_date
FROM author
CROSS JOIN LATERAL (
SELECT book.title, book.release_date
FROM book
WHERE book.author_id = author.id
ORDER BY book.release_date DESC
LIMIT 1
) AS latest_book;
Dear Negora!
Can you show me the usage with some short examples?Thanks for it!BRddnegora <public@negora.com> ezt írta (időpont: 2023. márc. 10., P, 9:43):Hi Chris:
You can use a subselect in the `from` and `join` clauses. That's how I get multiple columns from a single subselect.
If the subselect needs to use a reference from the outer scope (i.e. the main query) , you can use the `lateral` modifier.
Best regards.
On 10/03/2023 08:34, Durumdara wrote: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:withpre as ( select * from t .... ),sub as (select pre.*, (select u.id from u where ... limit 1) as last_u_idselect sub.*, u.value, u.type, u.nnn from subleft 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 regardsChris