Hi all,
Just a quick question. I need to select the most recent update in a table. I thought that the following would do it
“SELECT MAX(DATE_FORMAT(created, ‘%b %e’)) AS lastdate FROM mytable”;
However, I find that it has to be MIN but this doesn’t make sense to me.
Why is this so??
Genius! Of course. That is correct. It would be better I think to store the date as a time stamp, select for the max on that and then format the date for display.
Thanx for the replies. I’m sure I would have been scratching my head for some time otherwise 
. I love these communities. 
doh!!
i shoulda seen that
“most recent” means the latest date that is in the past (it can’t be in the future, right?)
MIN would give you the earliest date, i.e. the one farthest away from today
so therefore it has to be MAX
It probably has to do with DATE_FORMAT(created, ‘%b %e’)
%b is a the short name of the month, ie. Jan … Dec
Since the months of the year are not ordered alphabetically you indeed have Feb < Jan, while in actuality it’s the other way around.
Therefore I’d say both MIN and MAX don’t work, depending on the available data you can get unexpected results.
Could you make it DATE_FORMAT(MAX(created), ‘%b %e’) ?
I’m not 100% sure if that works, but if it does that should solve your problem
i will venture a guess and say that something else is wrong
if you would be kind enough to dump the table and post the CREATE TABLE statement along with a few INSERT statements…
Hi,
Thanks for the quick response. This is of course exactly what I thought, however, it is not doing that. It seems that the exact opposite is happening on my site. I noticed that the 1st date was being shown not the last date ie today when using MAX. I changed the script to MIN and it started showing the last date.
This was the reason for my question, I can’t work out why MIN gives me the last date and MAX gives the first date…very strange