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
Problem with SQL grouping
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
This is just a stab...
Code: Select all
SELECT orders_products.products_id, orders_products.products_model, orders.order_date
FROM orders INNER JOIN orders_products
ON orders.orders_id = orders_products.orders_id
WHERE orders.customers_id = 164
GROUP BY orders_products.products_id
ORDER BY orders.order_date DESC
LIMIT 0, 5-
MicahCarrick
- Forum Newbie
- Posts: 23
- Joined: Sat Apr 09, 2005 5:40 pm
Thanks, final solution that worked was to use the MAX function to pull up the latest date:
Code: Select all
SELECT products_id, products_model, MAX(order_date) order_date
FROM orders, orders_products
WHERE orders.orders_id = orders_products.orders_id
AND orders.customers_id = {$user->id}
GROUP BY products_id
ORDER BY order_date DESC
LIMIT 5- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
-
MicahCarrick
- Forum Newbie
- Posts: 23
- Joined: Sat Apr 09, 2005 5:40 pm