Sorting two columns in a single table using MySQL

So, someone might say that to sort two columns in a single table using MySQL the statement would look like this:

SELECT * FROM table ORDER BY column1,column2 ASC

This would ONLY sort by “column1” first and then “column2”. This is hardly what we’re after.

Say you had a column called “time” (that is also built using “time”) and a column called “date” (that is built using “date”). If we wanted to truly sort the rows in this table by date AND time we need to do a join like this:

SELECT * FROM (

( SELECT * FROM events ORDER BY date ASC)
UNION
( SELECT * FROM events ORDER BY time ASC)

) AS WOWZER WHERE some_column=’some_criteria'”;

Let’s break it down. First, we start a typical select statement (first line). Then we have a VERY simple join that says I want the date column and time columns joined as a column called “WOWZER”. Finally we only want certain criteria for this select.

This is how to truly sort using two columns, unfortunately ORDER BY will not work correctly with just a comma between the two sorts because the items being sorted would be independent of one another.

-Andy