CODEWITHSUNDEEP
sqlmysql
Samuel
Samuel

Reputation: 18586

MySQL query - ORDER BY

Can I do this?:

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

Upvotes: 2

Views: 150

Answers (3)

Martin Smith
Martin Smith

Reputation: 453990

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?

Upvotes: 4

Asaph
Asaph

Reputation: 162849

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.

Upvotes: 1

Tomasz Kowalczyk
Tomasz Kowalczyk

Reputation: 10477

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.

Upvotes: 2

Related Questions