Lists: | pgsql-novicepgsql-sql |
---|
From: | "Dan Langille" <dan(at)langille(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | using LIMIT only on primary table |
Date: | 2002-03-02 22:35:31 |
Message-ID: | 200203022235.g22MZYk55756@lists.unixathome.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice pgsql-sql |
If I want the last 100 orders:
SELECT * FROM orders LIMIT 100;
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
But that will only give me the last 100 items, not 100 orders.
What I really want is
SELECT *
from orders, order_items
where order_items.order_id = orders.id
and exists
(SELECT * from orders order by ID DESC limit 100);
But that gives me all orders, not just the first 100.
Adding a LIMIT 100 to the above doesn't work either. It equates to the
first example.
Clues? cheers
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
From: | daq <daq(at)ugyvitelszolgaltato(dot)hu> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: [SQL] using LIMIT only on primary table |
Date: | 2002-03-02 23:20:59 |
Message-ID: | 10613703764.20020303002059@ugyvitelszolgaltato.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice pgsql-sql |
Try this:
SELECT *
from order_items
where order_id in (select ID from orders order by ID desc limit 100);
DAQ
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | dan(at)langille(dot)org |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: using LIMIT only on primary table |
Date: | 2002-03-02 23:22:35 |
Message-ID: | 17340.1015111355@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice pgsql-sql |
"Dan Langille" <dan(at)langille(dot)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
regards, tom lane
From: | "Dan Langille" <dan(at)langille(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: using LIMIT only on primary table |
Date: | 2002-03-02 23:40:19 |
Message-ID: | 200203022340.g22NeNk56386@lists.unixathome.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice pgsql-sql |
On 2 Mar 2002 at 18:22, Tom Lane wrote:
> "Dan Langille" <dan(at)langille(dot)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
From: | Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> |
---|---|
To: | dan(at)langille(dot)org |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: using LIMIT only on primary table |
Date: | 2002-03-03 06:00:01 |
Message-ID: | 20020303141057.614C.RK73@sea.plala.or.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice pgsql-sql |
On Sat, 2 Mar 2002 17:35:31 -0500
"Dan Langille" <dan(at)langille(dot)org> wrote:
> If I want the last 100 orders:
>
> SELECT * FROM orders LIMIT 100;
>
> 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
>
> But that will only give me the last 100 items, not 100 orders.
>
> What I really want is
>
> SELECT *
> from orders, order_items
> where order_items.order_id = orders.id
> and exists
> (SELECT * from orders order by ID DESC limit 100);
This probably gives you all the items on the last 100 orders.
select *
from (select * from orders order by ID desc limit 10) as o
inner join order_items as oi
on (oi.order_id = o.order_id)
;
>
> But that gives me all orders, not just the first 100.
>
> Adding a LIMIT 100 to the above doesn't work either. It equates to the
> first example.
Regards,
Masaru Sugawara