how to sort "Day" on a Birthdate order by ASC?

[eluser]Unknown[/eluser]
can u help me this one?
sorry...i'm just new to codeigniter...hope u could help me ^___^

[eluser]Pascal Kriete[/eluser]
You'll need to give us a little more information.
How are you storing the date? As a timestamp or in a date field?

[eluser]gRoberts[/eluser]

Code:

select * from table order by month(birthdate) asc #(order by numeric month value)

or

Code:

select * from table order by monthname(birthdate) asc #(order by month name value)

[eluser]narkaT[/eluser]
using mysql-functions in ORDER BY statements isn't a good idea.
mysql will have to create a temporary table, insert the complete resultset,
sort the table and finally return the sorted resultset.
that's not very effective

[eluser]Unknown[/eluser]
[quote author="inparo" date="1224515278"]You'll need to give us a little more information.
How are you storing the date? As a timestamp or in a date field?[/quote]

i'm using a date field ^__^

[eluser]Pascal Kriete[/eluser]
You can find a list of functions and what they do here. In this case, DAYOFYEAR() is probably what you're looking for. You can use that in combination with gRoberts' code to sort the results.

@narkaT, what do you think should be used instead?

[eluser]narkaT[/eluser]
[quote author="inparo" date="1224702081"]@narkaT, what do you think should be used instead?[/quote]
normal field(s) where a index can be used by mysql would be perfect.


sorting by a monthname would be done by an additional field containing the monthname.
okay, that field has to be updated evertime the date gehts updated,
that would be about ~30 keystrokes more...

very small effort, maximum performance


for example:
I'm currently working on a sorted query joining two large tables (5.5 MB & 41,8 MB):

without index: 0.06 - 0.07 seconds
with index: 0.0004 seconds

on small tables the difference won't be that big and maybe I'm just thinking
in "too big dimensions"