mysqlnested-select

MySQL select most recent from multiple columns

I have a table that has multiple date columns, the following is an example of my table columns: tbl.user_id, tbl.date_1, tbl.date_2, tbl.date_3

I need to come up with a query that will return user_id and most_recent_date where most most_recent_date is the most recent date between date_1, date_2 and date_3. I think I need a nested select here but I can't work out the logic for it. I'm hoping someone could point me in the right direction here.

Solution

  • Have you tried the Greatest() function?

    Here would be a quick example:

    SELECT
    userId,
    greatest(dt1, dt2, dt3)
FROM testTbl;

    You probably have tried the Max() function like you would have in most programming languages. However, in SQL, Max is an aggregation function. You can only specify one field in it and it will return the biggest value in that column, grouped on whatever your GROUP BY is grouping.

    The Greatest() function in mySQL is the equivalent of the Max() function from most other languages.