MySQL query - ORDER BY

Question

Hello,

I was just wondering wether i can do this:

SELECT * FROM calendar ORDER BY ((month * 31) + day)

Answer 1

You can order by any column in your query result. If you can SELECT such on-the-fly prepared information then you can ORDER BY it.

Answer 2

Yes (assuming that you have numeric columns in your table called month and day)

Wouldn't you want

SELECT * FROM calendar ORDER BY ((`month` * 31) + `day`)

though?

Edit

Actually just use

SELECT * FROM calendar ORDER BY `month`, `day`

Unless I'm missing something?

Answer 3

If you have columns in your table called month and day, then you should make a few changes to your query and it will work:

SELECT * FROM calendar ORDER BY ((month * 100) + day)

I removed the quotes from your column names and increased your month multiplier by one order of magnitude in order to ensure correct sorting on month and day numbers greater than or equal to 10. Also, I corrected a typo in the spelling of ORDER.

BUT

• Your query will not sort correctly by year (not sure if this matters in your use case).
• Your query will execute slowly on large data sets because of the dynamic nature of your ORDER by clause. Consider using a single date column and creating an index on it.