# Thread: Selecting one of 3 columns...

1. Registered User
Join Date
Mar 2004
Posts
51

## Unanswered: Selecting one of 3 columns...

Hi,

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".

Thanks,

NB

2. Registered User
Join Date
Sep 2003
Posts
7
Try this,

SELECT
MAX ( CASE
WHEN DATE1 > DATE2 AND DATE1 > DATE3 THEN DATE1
WHEN DATE2 > DATE1 AND DATE2 > DATE3 THEN DATE2
WHEN DATE3 > DATE1 AND DATE3 > DATE2 THEN DATE3
END )
FROM
Table

3. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
The expresion fails if Date1=Date2=Date3, so you need to have:

SELECT
MAX ( CASE
WHEN DATE1 > DATE2 AND DATE1 > DATE3 THEN DATE1
WHEN DATE2 > DATE1 AND DATE2 > DATE3 THEN DATE2
ELSE DATE3
END )
FROM
Table

Alternatively, you can also consider to compute the three different maximums and (optionally) compute the maximum of these three dates:

SELECT max(D) FROM (
SELECT max(Date1) D, 'Date1' FROM <YourTable> UNION
SELECT max(Date2) D, 'Date2' FROM <YourTable> UNION
SELECT max(Date3) D, 'Date3' FROM <YourTable>
) T

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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.

5. Registered User
Join Date
Mar 2004
Posts
51
Very nice, thanks for the code!

6. Registered User
Join Date
Mar 2004
Posts
51
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
Last edited by nbozic; 12-29-04 at 11:48.

7. Registered User
Join Date
Mar 2004
Posts
51
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?

8. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Use the ISNULL() or COALESCE() functions.

9. Registered User
Join Date
Mar 2004
Posts
51
Excellent, exactly what I need! Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•