Problem with SQL grouping
Posted: Wed Jun 07, 2006 8:15 pm
What I want to do, is run a group query to get the last 5 ordered products. The orders_products table is tied to the orders table. The orders table is where I get the date so as to get the last 5 orders and orders_products contains the actual products. I don't want duplicate products. This query here however, does not give me the correct results as the grouping results in a product with the FIRST order in which it appears. Is there a way to sort the grouping so that the grouping uses the newest order.order_date?
SELECT orders_products.products_id, orders_products.products_model, orders.order_date
FROM orders, orders_products
WHERE orders.orders_id = orders_products.orders_id
AND orders.customers_id = 164
GROUP BY orders_products.products_id
ORDER BY order_date DESC
LIMIT 5
SELECT orders_products.products_id, orders_products.products_model, orders.order_date
FROM orders, orders_products
WHERE orders.orders_id = orders_products.orders_id
AND orders.customers_id = 164
GROUP BY orders_products.products_id
ORDER BY order_date DESC
LIMIT 5