Order by Datetime not quite working.

[poleposters]

Hi,

 

I'm having a problem ordering my results by date on a datetime field.

 

The result get ordered almost right, but not rightly enough. It seems to happen mostly where the results have a datetime a few seconds apart.

 

Is this a mysql bug? Or is it the wrong approach? Or is there a way around it?

 

Any help is appreciated.

 

Here is the query if its of any help

 

SELECT * FROM (

SELECT 'Order Created' AS event,invoice_date AS date_completed FROM orders WHERE order_id='$order_id'

UNION ALL

SELECT 'Customer added to system' AS event, customer_added AS date_completed FROM customers LEFT JOIN orders ON orders.customer_id=customers.customer_id WHERE order_id='$order_id'

UNION ALL

SELECT task AS event,task_added AS date_completed FROM tasks WHERE order_id='$order_id'

UNION ALL

SELECT CONCAT(amount,' received ',payment_method) AS event,payment_date AS date_completed FROM money_received LEFT JOIN payment_methods ON money_received.payment_method_id=payment_methods.payment_method_id WHERE order_id='$order_id'

UNION ALL 

SELECT CONCAT(product,' added to order') AS event, item_added AS date_completed FROM items LEFT JOIN products ON items.product_id=products.product_id WHERE order_id='$order_id'

UNION ALL

SELECT CONCAT(product,' arrived in warehouse') AS event,arrival AS date_completed FROM items LEFT JOIN inventory ON items.inventory_id=inventory.inventory_id LEFT JOIN shipments ON inventory.shipment_id=shipments.shipment_id LEFT JOIN products ON items.product_id=products.product_id WHERE order_id='$order_id' AND arrival <='$today'


) AS a ORDER BY date_completed ASC";

 

 

 

 

 

 

[PFMaBiSmAd]

You need to use just the date part of the value -

ORDER BY DATE(date_completed) ASC

[jskywalker]

The result get ordered almost right, but not rightly enough.

than you should change the ' ORDER BY' in your SQL statement.

(like PFMaBiSmAd suggested)

 

But after this change things get sorted on the date of date_completed only. If more records exist with same date what will happen?

 

You should add the field which is used to sort on after the DATE(date_completed) to mak sure records with same date get sorted like you wish tem to be sorted

 

i.e.:

ORDER BY DATE(date_completed), task ASC