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