Skip site navigation (1) Skip section navigation (2)

Re: Getting several columns from subselect with LIMIT 1

From: Marcus Engene <mengpg2(at)engene(dot)se>
To: Pierre Thibaudeau <pierdeux(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Getting several columns from subselect with LIMIT 1
Date: 2008-09-20 15:55:52
Message-ID: 48D51D08.7070004@engene.se (view raw or flat)
Thread:
Lists: pgsql-general
Pierre Thibaudeau wrote:
> I have a PERSONS table.
> I also have a PROGENY table, which is a many-to-many association table
> with two foreign keys to the PERSONS table to itself.
> (In this day and age, not only can an individual have any number of
> children, but also a person can have any number of parents!  At least,
> let's assume that's true for the sake of this setup.)
>
> Suppose I wish to construct a view of the persons, along with the name
> of their first-born (if they have one;  NULL otherwise).  The
> following SELECT does just that:
>
> SELECT
> 	persons.*,
> 	(
> 		SELECT child.name
> 		FROM progeny JOIN persons child ON child.id = progeny.child
> 		WHERE progeny.parent = persons.id
> 		ORDER BY child.birthdate ASC
> 		LIMIT 1
> 	) AS firstborn_name
>  FROM persons;
>
> Now, this is probably not the most elegant piece of code, but the real
> problem is that
> I cannot see how to extend it to the case where I want not only the
> firstborn's name but also the firstborn's ID
> (short of repeating the entire subselect a second time).  At the
> moment, with this current syntax, my subSELECT statement would not be
> allowed to return more than a single column.
>
> Any suggestion?
>
>   
Would this work?

select
    p.*
   ,pp.*
from
    persons p
   ,(
        SELECT child.name, child.id
        FROM progeny JOIN persons child ON child.id = progeny.child
        WHERE progeny.parent = p.id
        ORDER BY child.birthdate ASC
        LIMIT 1
    ) as kid(kid_name,kid_id)

best regards,
Marcus

In response to

Responses

pgsql-general by date

Next:From: Darren WeberDate: 2008-09-20 16:25:30
Subject: Re: [HACKERS] macport for libpqxx
Previous:From: Pierre ThibaudeauDate: 2008-09-20 15:36:44
Subject: Getting several columns from subselect with LIMIT 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group