SQL Max union (multiple columns)

[karimali831]

Hi

 

I'm trying to find the max value in 3 columns, new_date, reply_date and finalized_date in my table:

 

$test = safe_query("SELECT MAX(new_date) FROM (SELECT new_date AS new_date FROM ".PREFIX."cup_challenges UNION SELECT reply_date FROM ".PREFIX."cup_challenges UNION SELECT finalized_date FROM ".PREFIX."cup_challenges) AS maxval");

 

Did some research and have no idea what is wrong/if the above is correct?

The page breaks below this code.

[sasa]

SQL looks OK for me

[karimali831]

Hmm ok.

My other question is can I convert for example this time format : Tuesday Aug 31st 2010 @ 2:04 pm to a timestamp?

[DavidAM]

We really need a better description than "the page breaks".

 

$test = safe_query("SELECT MAX(new_date) 
    FROM (SELECT new_date AS new_date FROM ".PREFIX."cup_challenges 
    UNION SELECT reply_date FROM ".PREFIX."cup_challenges 
    UNION SELECT finalized_date FROM ".PREFIX."cup_challenges) AS maxval");

 

You are aliasing the psuedo table for your union as "maxval", when I think you actually wanted to alias the MAX(new_date) column:

 

$test = safe_query("SELECT MAX(new_date)  AS maxval
    FROM (SELECT new_date AS new_date FROM ".PREFIX."cup_challenges 
    UNION SELECT reply_date FROM ".PREFIX."cup_challenges 
    UNION SELECT finalized_date FROM ".PREFIX."cup_challenges)");

If that is not the problem, then give us a better description of what is happening.

 

By the way, you do realize you are doing three table scans there (I think). Unless those three date columns are indexed, this query will take a long time to run. I think this is one of the few cases where you might be better off doing a little of this on the front-end.

 

SELECT MAX(new_date), MAX(reply_date), MAX(finalized_date)
    FROM ".PREFIX."cup_challenges 

 

Then find the max of the three returned values - assuming you fetch the data into an array called $row:

 $maxDate = max($row);

 

That should result in a single table scan. Although, if the three date columns ARE indexed, your UNION solution might be faster. Use EXPLAIN and look through the plan.

 

As to converting the date string. Look at the strtotime() PHP function.

[sasa]

UNIX_TIMESTAMP(STR_TO_DATE(`date`,'%W %b %D %Y @ %h:%i %p'))

[karimali831]

I solved the time issue but posting timestamps to the database instead of the actual time and date format.

 

DavidAM: max(); worked fine, thanks alot.