| From: | "Pierre Thibaudeau" <pierdeux(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Getting several columns from subselect with LIMIT 1 | 
| Date: | 2008-09-20 15:36:44 | 
| Message-ID: | 74b035bb0809200836u6c3b86e7u3d98093a0c17555c@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Marcus Engene | 2008-09-20 15:55:52 | Re: Getting several columns from subselect with LIMIT 1 | 
| Previous Message | Joris Dobbelsteen | 2008-09-20 14:19:00 | Re: Oracle and Postgresql |