From: Lee Harr <missive@frontiernet.net>
To: pgsql-sql@postgresql.org
Subject: Re: using LIMIT only on primary table
Date: Sun, 28 Jul 2002 18:13:39 +0000 (UTC)
Message-ID: <ai1c8j$1htm$1@news.hub.org> (raw)
References: <3D42D7AA.27447.3EE190A0@localhost>
In article <3D42D7AA.27447.3EE190A0@localhost>, "Dan Langille" wrote:
> This is an extension of the problem solved by
> http://archives.postgresql.org/pgsql-sql/2002-03/msg00020.php but
> with a slightly different complication.
>
> I want to get the last 100 port commits from the database. Commits
> are stored in
> commit_log and commit_log_ports relates commits to ports. A given
> commit may
> affect more than one port (i.e. there is a 1-N relationship between
> commit_log and
> commit_log_ports).
>
> So a starting point for the last 100 port commits is:
>
> explain analyze
> SELECT distinct commit_log.*
> FROM commit_log_ports, commit_log
> WHERE commit_log.id = commit_log_ports.commit_log_id
> ORDER BY commit_log.commit_date DESC, commit_log_ports.commit_log_id
> LIMIT 100;
>
I am not sure if this will help, but how about a subselect?
SELECT DISTINCT commit_log.*
FROM commit_log_ports,
(SELECT commit_log.id
FROM commit_log
ORDER BY commit_log.commit_date DESC
LIMIT 100) AS commit_log
WHERE commit_log.id = commit_log_ports.commit_log_id
ORDER BY commit_log.commit_date DESC, commit_log.id
LIMIT 100;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: missive@frontiernet.net
Subject: Re: using LIMIT only on primary table
In-Reply-To: <ai1c8j$1htm$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox