Numbering issues with mysql

[SCook]

Hi all,

 

I know this is primarily a mysql question, but I think it has other uses also.  If I query my db and ask for the max(date) for example, everything works unless the highest date is January.  This is due to the fact that a computer wants to order numbers like this: 1, 11, 12, 2, 3, 4, ......

 

Is there s way around this failure?  I want to make sure I always get the furtherest date.  Dates are in 1/10/2008 format.  Thanks

[chigley]

Have you tried the ORDER BY statement?

[phpSensei]

<?php


   $latest_date = mysql_fetch_assoc(mysql_query("SELECT * FROM table ORDER BY `date` DESC LIMIT 1"));


   echo $latest_date;


?>

[corbin]

What's the datatype of the column?  If the column is varchar, that's how it's going to be sorted....

[SCook]

It is a varchar().  Thinking about it, I could've saved the date in a different format, but it is what it is.  I will try the order by, but I don't think that will help.  Simply because the way computers want to order things.

[chigley]

It should work...

[corbin]

If it's varchar, it will be sorted the same way text would be....

 

If they were yyyy/mm/dd you could probably wrap them in a DATE() function to sort them....  Hrmmm....

 

 

The reason it won't work is as follows:

 

1/1/08 is obviously before 12/1/08, but 2 is before / in alphabetical sorting, so it wins.

 

Edit: actually I'm not sure which order MySQL considers characters in, but I know for sure what's causing the problem in the first post:

 

When things are sorted alphabetically, first the first letter is sorted....

 

Example of original data:

aa

ba

ab

bc

ac

bb

 

First letter sort:

 

aa

ab

bc

bb

 

Second letter sort:

 

aa

ab

bb

bc

 

So with numbers it does the same thing:

 

Original:

1

11

2

27

24

3

 

First number:

 

1

11

2

27

24

3

 

Second number:

 

1

11

2

3

24

27

[revraz]

Then it's by design.  Change it to a one of the Number formats instead.

 

It is a varchar().  Thinking about it, I could've saved the date in a different format, but it is what it is.  I will try the order by, but I don't think that will help.  Simply because the way computers want to order things.

[corbin]

Hrmmm said this wrong earlier....

 

That last one should be:

 

1

11

2

24

27

3