CODEWITHSUNDEEP
mysqlsql-order-by
Anonymous
Anonymous

Reputation: 179

sql query complex ordering by issue

Hi I'm having problems with an SQL query. If I wanted output the table so that it displayed the products from latest comment made to earliest comment made...keeping in mind that if the output is product id 78, product id 79, product id 81, product id 78, product id 81, i want the output to be product id 78, product id 79, product id 81. Basically i'm trying to create a page showing the latest comment activity on a product.

SELECT comments.products_products_id, products.name, products.minilocation, products.users_user_id, 
comments.comment, comment_id, comments.time_stamp, users.username, users.miniavatar  FROM products
INNER JOIN comments ON comments.products_products_id = products.products_id INNER JOIN users ON users.user_id = comments.products_users_user_id
ORDER BY comments.time_stamp DESC

Upvotes: 0

Views: 135

Answers (1)

mathematical.coffee
mathematical.coffee

Reputation: 56905

Depends if you just want the most recent comment per product_id, or if you want all comments, but just sorted by product_id where the product with the latest comment is first.

If you want all comments, grouped by product_id, where the product_ids are sorted in order of latest comment (i.e. see how the latest time stamp order suggest the products are in order (3,1,2):

product_id    comment_timestamp  
   3              2012-03-13
   3              2012-03-09
   3              2012-03-01
   1              2012-03-12
   1              2012-01-01
   2              2012-03-11

Then here's how you can do it -- find out the latest timestamp per product, JOIN that onto your main query, and order by it:

SELECT comments.products_products_id, products.name, 
       products.minilocation, products.users_user_id, 
       comments.comment, comment_id, comments.time_stamp, 
       users.username, users.miniavatar  
FROM products
-- NEW JOIN:
INNER JOIN (SELECT product_id, MAX(time_stamp) as latest 
            FROM comments 
            GROUP BY product_id) latest 
      ON latest.product_id = products.product_id
-- as before.
INNER JOIN comments ON comments.products_products_id = products.products_id 
INNER JOIN users ON users.user_id = comments.products_users_user_id
-- NEW SORT:
ORDER BY latest.latest DESC, comments.time_stamp DESC

The new table joined to is the maximum (i.e. most recent) time stamp per product id. This will be the same within a single product id.

We sort by this first (to sort by the latest comment timestamp), and then sort by individual comment timestamp within each product id.

If you want the most recent comment per product_id, i.e.

product_id    comment_timestamp  
   3              2012-03-13
   1              2012-03-12
   2              2012-03-11

Then use:

SELECT comments.products_products_id, products.name, 
       products.minilocation, products.users_user_id, 
       comments.comment, comment_id, comments.time_stamp, 
       users.username, users.miniavatar  
FROM comments
-- NEW SELF-JOIN
LEFT JOIN comments c2 ON comments.products_products_id = c2.products_products_id
                     AND comments.time_stamp < c2.time_stamp
INNER JOIN products ON comments.products_products_id = products.products_id 
INNER JOIN users ON users.user_id = comments.products_users_user_id
-- new condition
WHERE c2.time_stamp IS NULL
ORDER BY comments.time_stamp DESC

This sort of query is called "greatest n per group", and you basically join your COMMENTS table to itself within each product_id. You also join it such that one table has time_stamps less than the other's. The WHERE condition selects rows such that there is no greater time stamp for that product_id, i.e. the latest comment.

Upvotes: 2

Related Questions