Order by with numbers?

dreams4000

Hey all,

I am trying to do an "order by" in a mysql query on some dates and some other large numbers. If I order by dates that look like 1/18/2004, 1/25/2004, and 1/4/2004, instead of putting them in actual dated order, it puts them in a numerical order as listed there. Is there any way to get it to order by the date value instead of the numerical value?

Also, this happens with numbers as well. If I have values like 100000, 20, 90 and 500, they will get orderd with the 1, 2, 5, and 9 numbers being in that order instead of their actual value. Is there any way to have them order with the 100000 as the highest and 20 as the lowest? Thanks!

LordShryku

Not sure about mysql being able to do that, but you can put them into an array and [man]natsort[/man] them.

drawmack

store your dates in year/month/day format then the order by clause will work and you can flip them back around in php like this:

<?php
//$date holds year/month/day formatted date
$date_ary = explode('/',$date);
$date = $date_ary[1] . '/' . $date_ary[2] . '/' . $date_ary[0];
//$date now holds month/day/year formatted date
?>

mrhappiness

you want to use DaTETIME / TIMESTAMP and INT instead of varcahr as column data type

dreams4000

Thanks for all the advice everyone. mrhappiness, I tried the changes you show there, but part of my issue is that the data is being dumped into my database from a different program and I use that data in my program. I do have it set right now as varchar and when I change it to the items you show, it does allow the sorting right, but I have numbers like 150.55 and when I change to anything, int, decimal etc.. it turns all the numbers to whole numbers without the decimal. I need to keep the numbers the same as they are dollar/cent values. The dates also get changed if I used the date format and the other system is dumping them as 1/5/2004. Any last ideas? Thanks!

Edit: I got the dollar/cent items to work with the Float setting, but still not much luck with the date item.

drawmack

is the other program dumping to a text file or directly to the database? If it is a text file then you can play with the dates before you insert them. If it is to the database then write a cron job that will fix the dates.

dreams4000

Drat.. I guess I'll have to stick with how it is for now if a cron job is the only way to do that one🙁 thanks for all the advice folks!

drawmack

well you could just do a php script but you'll have to remember to browse to it every day.

dopey

Hi,

I agree that if poss, you should get it in a datetime type. But, I think there's another way with a varchar:

select * from mytable order by concat(substring_index( mydate, '/', -1 ),'/',LPAD(substring_index(substring_in
dex( mydate, '/', 2 ),'/',1),2,'0'),'/',LPAD(substring_index(substring_index( mydate, '/', 2 ),'/',-1),2
,'0'));

basically, it splits up the date in mm/dd/yyyy format and puts it in yyyy/mm/dd in the order by clause. Could be shortened I think, but I'm not thinkin straight 🙂