Which is the GREATEST among you date columns?

Neither of the following two queries are doing what I’m trying to achieve. I need the most recent dated, of the three columns, to be set AS dateEdited.

Please advise.

select max(COALESCE(dateAuthorEdit, dateEditorEdit, dateAdminEdit)) AS dateEdited from testimonials where testimonialID = 8586;

select greatest(COALESCE(dateAuthorEdit, dateEditorEdit, dateAdminEdit)) AS dateEdited from testimonials where testimonialID = 8586;

You’re close.

MAX gets the highest value of a column.

GREATEST returns the highest of multiple values.

COALESCE returns the first NOT NULL,

So it sounds like I need to use GREATEST. But how do I overcome the fact that some of these date columns will be NULL?

with COALESCE functions

Got it. Thanks!

Maybe I don’t got it after all. At first I thought it was working, but now I’ve proven it doesn’t work if there is a null in the dateAuthorEdit column. Here is what I put together:

GREATEST(dateAuthorEdit,
COALESCE(dateEditorEdit, 0),
COALESCE(dateAdminEdit, 0)) as dateEdited

Here are the results:

Thanks!

what did you do to the other columns to handle a null that you didn’t do to this column?

Silly me. I didn’t realize that is what the zero was doing. It seems to be working now with this:

	GREATEST(COALESCE(dateAuthorEdit, 0),
		COALESCE(dateEditorEdit, 0),
			COALESCE(dateAdminEdit, 0)) as dateEdited

Thanks brother!

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.