retrieving primary key for row with MIN function
Hello everyone,
I need to retrieve PK (r.id in the query) for row with
MIN(r.start_date), but with a twist: I need to select only one record,
the one with minimum date.
Doing it like this does not solve the problem:
SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date,
r.id AS reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND
(r.start_date, r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
GROUP BY h.id, r.id
ORDER BY reservation_start_date ASC
I have to use either GROUP BY r.id or use MIN(r.id). MIN(r.id) doesn't
select the id from the row with corresponding MIN(r.start_date), so it's
useless, while GROUP BY r.id produces more than one row:
host_id reservation_start_date reservation_id
361 2009-05-11 38
361 2009-05-17 21
I need to select only row with reservation_id = 38.
I would rather not do subquery for every 'host' record, since there can
be a lot of them...
Regards,
mk
Could you end the query with a "LIMIT 1"?
SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, r.id AS
reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND
(r.start_date, r.end_date) OVERLAPS ('2009-04-29'::date,
'2010-04-29'::date)
GROUP BY h.id, r.id
ORDER BY reservation_start_date ASC
LIMIT 1
;
Show quoted text
On Wed, Apr 29, 2009 at 8:30 PM, Marcin Krol <mrkafk@gmail.com> wrote:
Hello everyone,
I need to retrieve PK (r.id in the query) for row with MIN(r.start_date),
but with a twist: I need to select only one record, the one with minimum
date.Doing it like this does not solve the problem:
SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, r.id AS
reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND (r.start_date,
r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
GROUP BY h.id, r.id
ORDER BY reservation_start_date ASCI have to use either GROUP BY r.id or use MIN(r.id). MIN(r.id) doesn't
select the id from the row with corresponding MIN(r.start_date), so it's
useless, while GROUP BY r.id produces more than one row:host_id reservation_start_date reservation_id
361 2009-05-11 38
361 2009-05-17 21I need to select only row with reservation_id = 38.
I would rather not do subquery for every 'host' record, since there can be a
lot of them...Regards,
mk--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Apr 29, 2009 at 6:30 AM, Marcin Krol <mrkafk@gmail.com> wrote:
Hello everyone,
I need to retrieve PK (r.id in the query) for row with MIN(r.start_date),
but with a twist: I need to select only one record, the one with minimum
date.Doing it like this does not solve the problem:
SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, r.id AS
reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND (r.start_date,
r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
GROUP BY h.id, r.id
ORDER BY reservation_start_date ASC
Couldn't you just use something like
select r.id from (join tables here) order by date asc limit 1
or something like that?
On Wed, Apr 29, 2009 at 5:30 AM, Marcin Krol <mrkafk@gmail.com> wrote:
I need to retrieve PK (r.id in the query) for row with MIN(r.start_date),
but with a twist: I need to select only one record, the one with minimum
date.
If you mean on row period then just add a limit 1 to the end of your
existing query.
SELECT h.id AS host_id, MIN(r.start_date) AS reservation_start_date, r.id AS
reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND (r.start_date,
r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
GROUP BY h.id, r.id
ORDER BY reservation_start_date ASC
LIMIT 1;
If you mean the minimum start_date for each group of r.id then try distinct on
SELECT DISTINCT ON ( r.id ) h.id AS host_id, r.start_date, r.id AS reservation_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND (r.start_date,
r.end_date) OVERLAPS ('2009-04-29'::date, '2010-04-29'::date)
ORDER BY r.id, r.start_date;
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug