Order by Date not working as expected in MySql

Question

I have a mysql query

    select count(*) as TotalCount,
           pd.Product_Modified_Date,
           psc.Product_Subcategory_Name,
           pd.Product_Image_URL
      from product_subcategory psc
inner join product_details pd on psc.Product_Subcategory_ID = pd.Product_Subcategory_Reference_ID
     where pd.Product_Status = 0 and
           psc.Product_Subcategory_Status = 0
  group by psc.Product_Subcategory_Name
  order by pd.Product_Modified_Date desc

In my product_details table have new image urls. But i could not get it by the above query.

How can i do it?

Looks like what ever is populating the `Product_Modified_Date` is populating it wrong. This also seems like just a MySQL question. Is there a PHP/mysqli issue? — chris85, Jul 28 '16 at 19:09
What output would you prefer? If the first row represents 172 records with the same name, which of the (possibly different) 172 dates and urls would you like to show for that row? — showdev, Jul 28 '16 at 19:17

score 2 · Answer 1 · answered Jul 28 '16 at 19:14

You are grouping by one column, Product_Subcategory_Name, but you have other columns Product_Image_URL and Product_Modified_Date in your select-list.

If you have cases where the group has multiple rows (which you do, since the count is 14 or more in each group), MySQL can only present one value for the Product_Image_URL. So it picks some row in the group, and uses the value in that row. The URL value for all other rows in the group is ignored.

To fix this, you must group by all columns in your select-list that are not part of an aggregate function. Any column you don't want to use to form a new group must go into an aggregate function.

Roland Bouman wrote an excellent blog detailing how to use GROUP BY properly: http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

Combining GROUP BY and ORDER BY is problematic and your problem is most likely covered in another question on Stack Exchange : MySQL wrong results with GROUP BY and ORDER BY

edited May 23 '17 at 12:32

Community

1
1

answered Jul 28 '16 at 19:14

stewe

68
1
11

Order by Date not working as expected in MySql

2 Answers2