Search Postgresql Archives

Re: Get more columns from a lookup type subselect

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux