Thread: using LIMIT only on primary table
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
"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 regards, tom lane
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
On Sat, 2 Mar 2002 17:35:31 -0500 "Dan Langille" <dan@langille.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