I have a table that has 3 different types of dates (date1, date2, date3), and they represent the edit times of the 3 different sections on the website.
How do I select only one of those three that's the most recent? (It would represent the most recent edit on the website overall).
It would be an easy task if it was only one column - I would simply select the MAX, but I have to be selective between 3 different columns and pick the one I "like".
The MAX is only necessary if you want to find the most recent change across all of your records, which is not what I read from your original post.
Note that all these complications occur because you are starting with a non-normalized data set. Such schemas almost always require complicated coding to work around their flaws. What are you going to do if sometime in the future you have to work with websites that have four or more sections?
Do yourself a favor and fix the design.
If it's not practically useful, then it's practically useless.
blindman, it is true that the coding is getting a bit out of control for this particular query. But... the current website structure, overall functionality needs, and types of records don't permit me to make it a different/better way. If I did change the structure to suit this particular query, it would probably make 50 other things much worse.
But yes, I exactly get your point:
Crappy design = Crappy & complex code = Crappy & slow system
I ran into some problems with the WHEN statements. If one or more of the 3 date fields are NULL, then the WHEN statements fail and the ELSE statement evaluates each time (because NULL values cannot be compared with date values in the query)
It works fine if none of the dates are NULL - but I have to keep them as NULL by default if the sections on the website haven't been updated yet.
Any ideas on how to get around this problem without adding many more WHEN statements?