Sort using dates from three different columns

[busboy]

I have a table that uses dateAuthorEdit, dateEditorEdit, dateAdminEdit. Rows in this table will always have a date in one or more of those 3 columns. Is a CASE clause the best way to approach this? I tried to put it together but it errors out.

CASE
	when t.dateAdminEdit is not null then t.dateAdminEdit 
	when t.dateEditorEdit is not null then t.dateEditorEdit
	when t.dateMemberEdit is not null then t.dateMemberEdit as dateEdited

Thanks!

[molona]

I don’t know which database you’re using. I assume that there is an END after this code.

Also, I’m not 100% sure which you’re trying to achieve. I mean, what happens when both dateAuthorEdit and dateEditorEdit do have a date?

I would say that a CASE is the best approach, at least with the information I have right now

[busboy]

@busboy

I assume that there is an END after this code.

Looks like I was missing the END part of the clause. Thanks for your help!!

[r937]

@busboy

CASE when t.dateAdminEdit is not null then t.dateAdminEdit when t.dateEditorEdit is not null then t.dateEditorEdit when t.dateMemberEdit is not null then t.dateMemberEdit as dateEdited

this can be substantially simplified –

COALESCE(t.dateAdminEdit,t.dateEditorEdit,t.dateMemberEdit) AS dateEdited