Received: from lists.unixathome.org (lists.unixathome.org [210.48.103.158])
	by postgresql.org (Postfix) with ESMTP id A9A5B475991
	for <pgsql-sql@postgresql.org>; Sat,  2 Mar 2002 18:40:31 -0500 (EST)
Received: from wocker (lists.unixathome.org [210.48.103.158])
	by lists.unixathome.org (8.11.6/8.11.6) with ESMTP id g22NeNk56386;
	Sun, 3 Mar 2002 12:40:23 +1300 (NZDT)
	(envelope-from dan@lists.unixathome.org)
Message-Id: <200203022340.g22NeNk56386@lists.unixathome.org>
From: "Dan Langille" <dan@langille.org>
Organization: DVL Software Limited
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 2 Mar 2002 18:40:19 -0500
MIME-Version: 1.0
Subject: Re: using LIMIT only on primary table 
Reply-To: dan@langille.org
Cc: pgsql-sql@postgresql.org
References: <200203022235.g22MZYk55756@lists.unixathome.org> 
In-reply-to: <17340.1015111355@sss.pgh.pa.us>
X-mailer: Pegasus Mail for Windows (v4.01)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
X-Archive-Number: 200203/21
X-Sequence-Number: 6903

On 2 Mar 2002 at 18:22, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> > If I want all the items on the last 100 orders, I'd start like this:
> 
> > SELECT * 
> > from orders, order_items 
> > where order_items.order_id = orders.id
> > LIMIT 100
> 
> I think you want
> 
> SELECT * 
> from
>   (SELECT * from orders order by ID DESC limit 100) as recent_orders,
>   order_items 
> where order_items.order_id = recent_orders.id

That is it Tom.  Thank you.  As soon as I saw your solution, I thought of 
creating a view to do this.

   CREATE VIEW recent orders AS 
   SELECT * from orders 
   order by ID DESC 
   limit 100;

Which makes the query:

   SELECT * 
   from recent_orders, order_items 
   where order_items.order_id = recent_orders.id

Cheers
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples