Search Postgresql Archives

Re: Getting several columns from subselect with LIMIT 1

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

 



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


[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 Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux