0

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.

full-stack
  • 553
  • 5
  • 20
Ross
  • 112
  • 1
  • 17
  • 1
    Seriously consider revising your schema. Any time you find yourself with enumerated columns (above, say, 2), you can be sure that your design is sub-optimal. – Strawberry May 01 '18 at 15:13
  • 1
    My answer doesn't cover Strawberry's comment, as it just directly answers the question, but yeah... even just 2 numbered fields is an indication of a bad schema... Depending on what you are trying to do, I'd suggest you make sure your schema is good. For example, if your 3 dates are different types of dates, for example there could be a deadline date for something... and a date at which it was effectively done, and you might want to pull out the most recent of the two dates. If, however, these are literally the same logical value that you just want to keep 3 of them... thats no good! – Mathieu Turcotte May 01 '18 at 15:30
  • Thanks - the table I was using is just an example. The table is actually a tmp table created from another query – Ross May 03 '18 at 08:08

1 Answers1

0

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.

Mathieu Turcotte
  • 344
  • 2
  • 14