First the lecture – (this is the price for the answer) – your table is not normalized properly. anytime you have “repeating” columns, it violates first normal form.
You should understand the rules of normalization and consider the advantages before designing any tables. I would suggest that your table, even though it exists in an RDBMS, is not truly a relational table.
and now the answer-
Here is one of my “samples” (it includes the lecture also) –
— FIND THE HIGHEST VALUE OF ANY TWO VALUES
— FROM DIFFERENT COLUMNS IN SAME TABLE
— OR EVEN DIFFERENT TABLES
— COMENTARY – WHY WOULD ANYONE WANT TO COMPARE TWO COLUMNS
— FROM THE SAME TABLE. THE MEANINGS OF THE VALUES IN ANY
— TWO COLUMNS IN THE SAME TABLE WOULD NOT (REPEAT NOT) HAVE
— ANY SIGNIFICANCE TO EACH OTHER. ONE MIGHT SAY COMPARING
— COLUMN “SATURDAY_SALES” WITH “SUNDAY_SALES”. AND I WOULD
— SAY THE DESIGN OF THAT TBLE VIOLATES FIRST NORMAL FORM.
— BUT LOOK.
— THESE ARE JUST CONTRIVED EXAMPLES – INSTEAD OF TAKING
— MY EXAMPLES LITERALLY, REMEMBER THEY ARE JUST EXAMPLES
— OF WAYS TO USE SQL.
—
—* FIND THE HIGHEST VALUE OF ANY VALUE IN EITHER COLUMN
SELECT CASE WHEN MAX1 > MAX2
THEN MAX1
ELSE MAX2
END AS COL3
FROM ( SELECT MAX(COL1) AS MAX1
FROM TTEST
) AS T1
JOIN ( SELECT MAX(COL2) AS MAX2
FROM TTEST
) AS T2
ON 1=1
;
—
—* ALTERNATIVE WHEN USING JUST ONE TABLE
—* FIND THE HIGHEST VALUE OF ANY VALUE IN EITHER COLUMN
SELECT CASE WHEN MAX1 > MAX2
THEN MAX1
ELSE MAX2
END AS COL3
FROM ( SELECT MAX(COL1) AS MAX1
,MAX(COL2) AS MAX2
FROM TTEST
) AS T1
;
Now, wouldn’t it easier to have designed the table with ONE date per row? Then the SQL is merely SELECT MAX(date_col) …
I hope this helps.
Steve
——————–
Unfortunately, determining the highest of 12 values with a CASE, would be slightly complex.
Another option to get the highest date <b>for each record</b>, could be an ugly query like this:
<pre>SELECT id,MAX(the_date)
FROM (
SELECT id,d1 the_date FROM table1
UNION ALL
SELECT id,d2 FROM table1
UNION ALL
SELECT id,d3 FROM table1
UNION ALL
SELECT id,d4 FROM table1
...)
GROUP BY id;</pre>
Other option could be to write a function to get the highest date for a single record, and use it in the query, but I don’t know how that would be in Access.
-CarlosDL
Discuss This Question: 4  Replies
or perhaps this could work ...