Re: using LIMIT only on primary table - Mailing list pgsql-sql

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_dateDESC   LIMIT 100) AS commit_logWHERE commit_log.id = commit_log_ports.commit_log_idORDER BY
commit_log.commit_dateDESC, commit_log.id LIMIT 100;