Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1paYLY-00043M-Va for pgsql-general@arkaria.postgresql.org; Fri, 10 Mar 2023 08:43:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1paYLX-0000sz-4N for pgsql-general@arkaria.postgresql.org; Fri, 10 Mar 2023 08:43:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1paYLW-0000sp-NB for pgsql-general@lists.postgresql.org; Fri, 10 Mar 2023 08:43:26 +0000 Received: from smtp-relay-02-5.dondominio.net ([31.214.176.38]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1paYLP-0005Ym-BG for pgsql-general@lists.postgresql.org; Fri, 10 Mar 2023 08:43:26 +0000 Received: from localhost (smtp-relay-local.scip.local [127.0.0.1]) by smtp-relay.dondominio.com (Postfix) with SMTP id 9787D406D1 for ; Fri, 10 Mar 2023 09:43:14 +0100 (CET) Received: from mail-node.dondominio.com by smtp-relay.dondominio.com (Postfix) with ESMTP id 21EE841142 for ; Fri, 10 Mar 2023 09:43:12 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=negora.com; s=dddk; t=1678437792; bh=3cDexCZ8SepHYuZ9OMlLvoM7zf+JiSpNKcZQAyJsn4Q=; h=Content-Type:Message-ID:Date:Subject:To:From; b=cQrV8eCa/hxTmiuynQ5dPQU6X21IlfkNALK8jSE87pxvpxu8Xsq1Yevzj2r3+FBtg s2dUJEsw7D51T6yn6kwzTg870KfWvA9jN1ZKp2C6j6kuegYxc3PLW9NvzWhq9X/9CM VenLLMs9EDJVie16yvbzEwXA29DaTzE2itNckbH4= Received: from [10.8.0.6] (61.red-83-58-241.dynamicip.rima-tde.net [83.58.241.61]) (Authenticated sender: public@negora.com) by mail-node.dondominio.com (Postfix) with ESMTPA id 01120423A3 for ; Fri, 10 Mar 2023 09:43:11 +0100 (CET) Content-Type: multipart/alternative; boundary="------------TGN70HqjyBQ86fs8LhqUclOA" Message-ID: <906d7125-2de3-bcb9-6a64-c0f58d8a0017@negora.com> Date: Fri, 10 Mar 2023 09:43:11 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: Get more columns from a lookup type subselect Content-Language: en-US, es-ES, en-GB To: pgsql-general@lists.postgresql.org References: From: negora In-Reply-To: X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.6.2 (mail-node.dondominio.com [0.0.0.0]); Fri, 10 Mar 2023 09:43:12 +0100 (CET) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------TGN70HqjyBQ86fs8LhqUclOA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------TGN70HqjyBQ86fs8LhqUclOA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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
--------------TGN70HqjyBQ86fs8LhqUclOA--