SQL Query to return a single value for each record based on max FIELD, not max record
I have a table with 12 date columns per record (D1 thru D12) and I want to select from each record, the max date of those columns (not the max of the records). I know:
select max(D1) from table1
will yield max of D1, but say I want D3 from record1, D1 from record2, etc (using the following example)
ID,D1,D2,D3,D4...
1, ,12-Apr-2007,24-Dec-2008, ,...
2,13-Sep-2009, , , ,...
Software/Hardware used:
MS Access 2007
select max(D1) from table1
will yield max of D1, but say I want D3 from record1, D1 from record2, etc (using the following example)
ID,D1,D2,D3,D4...
1, ,12-Apr-2007,24-Dec-2008, ,...
2,13-Sep-2009, , , ,...
Software/Hardware used:
MS Access 2007
ASKED:
December 4, 2009 7:59 AM
UPDATED:
December 24, 2009 3:11 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
1. There are various ways (via code or by query) to solve this problem, regardless of whether the schema meets third normal form, or is changed as described below. There are also other important issues to consider before automatically assuming that your schema should be rewritten into separate tables.
2. As for a query-based solution, there may be very well be some arcane SQL expression that compares and selects a min or max value from a set of columns of similar data types in a single row. I’d review some SQL reference books to see if such a query exists. If not, and if you retained your single-table schema, I think you’d have to use a query to create a temporary schema that emulates the three table approach described in par. 5, below.
3. As for a code solution, I would create a recordset, iterate through each record, place the five dates from each record into an array, sort the array and get the date value of the first item in the array (or just directly get the highest value in the array). This would handy if you wanted to record the result in another table or use it in your application logic.
4. AS TO THE NORMAL FORM OF YOUR SCHEMA: I don’t agree that the use of 12 date columns by itself NECESSARILY violates third normal form. If D1 through D12 represent unique types of information (such as a fixed and known set of milestones in some process), then the table is normalized. This would be true even if a very large number of milestone types were being modeled, so long as those types are not redundant in any way. (An example of redundant columns would be current and former addresses, which are better handled in a detail table with start and end date fields.)
Of course, if the number of milestone types was large, e.g., D1-D324, then putting those types in a separate table and recording specific milestone dates in a child table, as described below, would be appropriate way to improve performance and might even be necessary to stay within the table design specs of the database system (i.e., max. no. o f columns).
5. On the other hand, if the number of milestone types was or might be variable, or is unknown, then the schema would not be normalized, and should be revised to include at least three tables: The original table, a milestone types table and a milestone dates child table, with pointers back to the first two tables. But a multi table approach poses other design issues: First, does the app require that for each record in the original master table, a milestone date for each type of milestone be recorded? Second, would child records be required or permitted to be initialized or later set to NULL for any purpose? These activities are in addition to your original issue, which would require a query to find the highest date value among the D1-D12 child records for a given parent record in the original table.
6. Relatively trivial denormalization is acceptable when required to improve performance or convenience. For example, if your app requires that some or all of the D1-D12 values be entered in order to create a valid record, your single table approach works beautifully because you can easily indicate which fields must be entered (and set default values). It also makes it easier to write code behind a form (or maybe a table macro under Access 2010) to apply business rules that govern the relative values of D1-D12 (e.g., D5 must be later than D8).
Not knowing what your app does, I can’t say whether any performance benefit would be obtained. If there is even a small chance that the number of milestone types might changed to some unknown value greater than 12, then I think I’d use the multitable approach in order to future proof the application. It might be alot more work now, but it would require very little additional work when and if the number of milestone types had to be changed.
Hope this helps. Good luck!
regarding DbStudent’s comment #4 …
I would agree that having 12 dates in one row does not NECESSARILY violate first normal form. However, when one is comparing these dates to each other it seems to imply that they are signigant to each other which implies they are repeating columns.