Page 1 of 1

Problem with SQL grouping

Posted: Wed Jun 07, 2006 8:15 pm
by MicahCarrick
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

Posted: Fri Jun 09, 2006 12:44 am
by RobertGonzalez
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

Posted: Fri Jun 09, 2006 2:33 pm
by MicahCarrick
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

Posted: Fri Jun 09, 2006 3:18 pm
by RobertGonzalez
Man, I can be a blank sometimes. I totally forgot about that function... :x

Posted: Fri Jun 09, 2006 3:35 pm
by MicahCarrick
I don't know why I didn't think of it yesterday. Must've been late. :) I appreciate the help though.