Problem with SQL grouping

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MicahCarrick
Forum Newbie
Posts: 23
Joined: Sat Apr 09, 2005 5:40 pm

Problem with SQL grouping

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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
MicahCarrick
Forum Newbie
Posts: 23
Joined: Sat Apr 09, 2005 5:40 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Man, I can be a blank sometimes. I totally forgot about that function... :x
MicahCarrick
Forum Newbie
Posts: 23
Joined: Sat Apr 09, 2005 5:40 pm

Post by MicahCarrick »

I don't know why I didn't think of it yesterday. Must've been late. :) I appreciate the help though.
Post Reply