CODEWITHSUNDEEP
mysqldatetime
whymatt
whymatt

Reputation: 27

MYSQL Order by datetime outputs alphabetically instead of chronologically

I'm using the following query to return rows ordered by a datetime field.

SELECT DISTINCT firstname, surname, email, DATE_FORMAT(dateregistered,'%y %b %d') AS dateregistered FROM clients, registrations, courses
WHERE clients.id = registrations.clientid
ORDER BY dateregistered

but it returns

13 Nov 05

14 Apr 01

14 Feb 25

14 Mar 01

instead of:

13 Nov 05
14 Feb 25
14 Mar 01
14 Apr 01

It seems to be ordering alphabetically based on the month.

How can I get it to order on the actual date?

Upvotes: 0

Views: 209

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The problem is that you have reformatted that date column and given it the same name. To fix this, use a table alias in the order by clause:

SELECT DISTINCT firstname, surname, email,
       DATE_FORMAT(dateregistered,'%y %b %d') AS dateregistered
FROM clients c join
     registrations r
     on c.id = r.clientid cross join
     courses
ORDER BY r.dateregistered;

Notice that I clarified the join's in the query. This structure is rather strange. I think you are missing a join condition on courses.

Upvotes: 0

Cody Caughlan
Cody Caughlan

Reputation: 32748

Use a different projection in the SELECT clause:

... DATE_FORMAT(dateregistered,'%y %b %d') AS formattedDateRegistered

Because you're casting the column the column to the same name as itself its confusing mysql.

So a final query could be:

SELECT DISTINCT firstname, surname, email,
DATE_FORMAT(dateregistered,'%y %b %d') AS formattedDateregistered
FROM clients, registrations, courses
WHERE clients.id = registrations.clientid
ORDER BY dateregistered

Upvotes: 1

Related Questions