Page 1 of 1

using ORDER_BY on two columns. the first being a date

Posted: Wed May 20, 2009 4:49 am
by deejay
Hi

What I am trying to do is get my results to return in date order firstly and then by alphabetical order.
I thought I'd be able to do this by adding 'ORDER BY b.arrival_date ASC, c.name ASC' to my query. And that does put some in the order I wish but not all.

I then thought that it must have something to do with the date's that are stored in mysql under the arrival_date coloumn, but they are exactly the same ie have no hours, seconds attached.

Any ideas welcome .

Code: Select all

 
$query = "SELECT b.arrival_date, b.departure_date, c.name, b.rid FROM booking_admin AS ba LEFT JOIN bookings AS b ON ba.booking_id = b.booking_id LEFT JOIN customers AS c ON b.cust_id = c.cust_id WHERE b.arrival_date >= '$date_commencing_original' AND b.arrival_date <= '$date_ending_original' AND ba.status != 'cancelled' AND ba.status != 'denied' AND ba.status != 'historic'  ORDER BY b.arrival_date ASC, c.name ASC ";
 
Thanks

Re: using ORDER_BY on two columns. the first being a date

Posted: Wed May 20, 2009 4:57 am
by onion2k
The fact you're storing your statuses as strings makes me wonder what you're storing the dates as. Are they strings too? Or are they actual proper dates?

Re: using ORDER_BY on two columns. the first being a date

Posted: Wed May 20, 2009 7:13 am
by deejay
hi

yeah the field is a 'date'. 0000-00-00

Re: using ORDER_BY on two columns. the first being a date

Posted: Wed May 20, 2009 7:47 am
by crazycoders
Can you give us an example of the sorting errors your have. I think it might be a collation issue in this case!

Re: using ORDER_BY on two columns. the first being a date

Posted: Wed May 20, 2009 6:02 pm
by deejay
an example of the kind of result i'm getting ( all on same date)
Curt
Robin
Cam
Cam
mull
Purt
Smith
the collation is ' latin1_swedish_ci' in both tables.

Thanks for any advice.