MySQL merge multiple date columns into one

Ok so I have multiple datetime columns for things such as date_created, last_login, last_updated, etc... I would like to merge them into one column with the most recent datetime showing, so that I can track active_users more easily.

I've found some suggestions to use the CONCAT comman开发者_Python百科d, but that looks to be just stringing all the results together.

Try this:

SELECT username, 
       GREATEST(date_created, last_login, last_updated) last_activity
FROM your_table
ORDER BY last_activity DESC

EDITED:

SELECT username, 
       GREATEST(
           IFNULL(date_created, 0), 
           IFNULL(last_login, 0), 
           IFNULL(last_updated, 0) 
       last_activity
FROM your_table
ORDER BY last_activity DESC

EDITED AGAIN:
On your db copy (having 27.451 records) I used:

SELECT id, 
       GREATEST(
           IFNULL(current_login_datetime, 0), 
           IFNULL(created_datetime, 0), 
           IFNULL(updated_datetime, 0)) 
       last_activity
FROM users
ORDER BY last_activity DESC

obtaining exaclty 27.451 records!!
To prove, run this query:

SELECT COUNT(*) FROM
(SELECT id, 
       GREATEST(
           IFNULL(current_login_datetime, 0), 
           IFNULL(created_datetime, 0), 
           IFNULL(updated_datetime, 0)) 
       last_activity
FROM users
ORDER BY last_activity DESC) der

and check that number returned is the same as the query

SELECT COUNT(*) FROM users

Your problem could be derived from a limit in total returned or showed records.
For example using Navicat light you can have 1000 records (but you can read 27.451 as total).

If the values can be null and you want to select the first non null value use COALESCE. GREATEST and LEAST are also options

SELECT 
  username
  ,COALESCE(last_updated,last_login,date_created) AS not_null
  ,GREATEST(date_created, last_login, last_updated) AS latest
  ,LEAST(date_created, last_login, last_updated) AS first
FROM table1
ORDER BY last_activity DESC

If you want more control, you can use a CASE WHEN statement:

SELECT
  username
  , CASE WHEN date_created > '2011-01-31' THEN date_created
         WHEN last_login > '2011-01-31' THEN last_login
         ELSE last_updated END as its_complicated
.....

If you want the lastest AND you have null values floating around, use this query:

SELECT 
  username
  ,GREATEST(IFNULL(date_created,0), IFNULL(last_login,0), IFNULL(last_updated,0)) AS latest
FROM table1
ORDER BY last_activity DESC