Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id ADBF147585D
	for <pgsql-sql@postgresql.org>; Sun, 28 Jul 2002 14:14:00 -0400 (EDT)
Received: by news.hub.org (Postfix, from userid 8)
	id 40152381C1D; Sun, 28 Jul 2002 14:13:39 -0400 (EDT)
From: Lee Harr <missive@frontiernet.net>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Re: using LIMIT only on primary table
Date: Sun, 28 Jul 2002 18:13:39 +0000 (UTC)
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 37
Message-ID: <ai1c8j$1htm$1@news.hub.org>
References: <3D42D7AA.27447.3EE190A0@localhost>
Reply-To: missive@hotmail.com
X-Complaints-To: news@hub.org
User-Agent: slrn/0.9.7.3 (FreeBSD)
To: pgsql-sql@postgresql.org
X-Archive-Number: 200207/435
X-Sequence-Number: 8813

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;