Hi,
Thanks for your answers. I hope stethoscope will help.
One more question, not relevant to the previous one but I hope it's OK not to open a new thread.
I'm trying to run the BestSellers query from TPC-W on MonetDB.
The query works as follows:
- Take the smallest order id from the most recent 3333 orders.
- Then take all order lines more recent than that, sum their quantities by item, and display the top 50 by total quantity.
Here is some SQL code for this:
SELECT i_id,
i_title,
a_fname,
a_lname
FROM item,
author,
order_line
WHERE ol_o_id > (SELECT Min(o_id)
FROM (SELECT o_id
FROM orders
ORDER BY o_date DESC
LIMIT 3333) s)
AND i_id = ol_i_id
AND i_a_id = a_id
AND i_subject = 'HOME'
GROUP BY i_id,
i_title,
a_fname,
a_lname
ORDER BY Sum(ol_qty) DESC
LIMIT 50;
I'm having trouble in expressing the nested sub-query:
SELECT MIN(o_id) FROM (SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 3333) AS s;
Searching in previous mails in this list, I found out that MonetDB does not support ORDER BY or LIMIT in subqueries.
Is there any workaround ?
Cheers!
Dimitris