CODEWITHSUNDEEP
mysqlsortingconditional-statements
csm
csm

Reputation: 65

Conditional MySQL order by two (equally important) columns

I have a list of products with lots of columns but the 3 most important for my problem are these (with some random data):

|--------------------------------------|
| title     | category_id | date       |
| ----------|-------------|------------|
|  Product1 |           1 | 2012-04-18 |
|  Product2 |           1 | 0000-00-00 |
|  Product3 |          17 | 0000-00-00 |
|  Product4 |          17 | 2012-04-10 |
|  Product5 |          17 | 2012-04-20 |
|  Product6 |           1 | 0000-00-00 |
|  Product7 |           2 | 2012-04-20 |
|  Product8 |           2 | 0000-00-00 |
|  Product9 |          17 | 2012-04-16 |
| Product10 |          22 | 2011-11-26 |
| Product11 |          22 | 2011-12-25 |
| Product12 |          30 | 2012-04-01 |
| Product13 |           2 | 2011-12-31 |
| Product14 |          30 | 2010-05-06 |
|--------------------------------------|

The products with the same category_id should be listed one after another (at this point this could be even solved by "ORDER BY category_id"), BUT I have to take care about the date column as well: the category_id and the products within the categories have to be sorted by date descending (the category_id with the newest product on top and so on), so ideally the resultset should be something like this (added line breaks between category "groups" just to be more transparent):

|--------------------------------------|
| title     | category_id | date       |
| ----------|-------------|------------|
|  Product5 |          17 | 2012-04-20 |
|  Product9 |          17 | 2012-04-16 |
|  Product4 |          17 | 2012-04-10 |
|  Product3 |          17 | 0000-00-00 |

|  Product7 |           2 | 2012-04-20 |
| Product13 |           2 | 2011-12-31 |
|  Product8 |           2 | 0000-00-00 |

|  Product1 |           1 | 2012-04-18 |
|  Product2 |           1 | 0000-00-00 |
|  Product6 |           1 | 0000-00-00 |

| Product12 |          30 | 2012-04-01 |
| Product14 |          30 | 2010-05-06 |

| Product11 |          22 | 2011-12-25 |
| Product10 |          22 | 2011-11-26 |
|--------------------------------------|

Would it be possible to get this resultset with only one query and what would be a working solution?

Thanks in advance, marcell

Upvotes: 1

Views: 824

Answers (2)

david.s
david.s

Reputation: 11403

So you first want to sort by the date which is the max date within the group of products with the same id, then you want to sort the dates within the groups.

Use this:

SELECT title, category_id, date
FROM table_name t
ORDER BY
    (SELECT Max(Date)
     FROM table_name
     WHERE title = t.title
     GROUP BY category_id) DESC,
    date DESC

Instead of title you can use the product_id in the inner sql query.

Upvotes: 0

piotrm
piotrm

Reputation: 12356

You need to find latest date for each category in a subquery, join this subquery to your table and order by 3 fields:

SELECT p.* FROM products p
JOIN
( SELECT category_id, MAX(date) as category_date FROM products
  GROUP BY category_id ) pg
ON p.category_id = pg.category_id
ORDER BY pg.category_date DESC, p.category_id, p.date DESC

Upvotes: 1

Related Questions