SQL Query to return a single value for each record based on max FIELD, not max record

5 pts.
Tags:
Microsoft Access
Microsoft Access 2007
SQL
SQL Query
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DbStudent
    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!
    10 pointsBadges:
    report
  • Meandyou
    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.
    5,220 pointsBadges:
    report
  • philpl1jb

    or perhaps this could work ...

     
    Select field1, field2, 
     Case 
       when d1 > d2 and d1 > d3 and d1 > d4 
         and d1 > d5 and d1 > d6 and d1 > d7 
         and d1 > d8 and d1 > d9 and d1 > d10 
         and d1 > d11 and d1 > d12 then d1
    
       when d2 > d3 and d2 > d4 
         and d2 > d5 and d2 > d6 and d2 > d7 
         and d2 > d8 and d2 > d9 and d2 > d10 
         and d2 > d11 and d2 > d12 then d2
      
      when d3 > d4 
         and d3 > d5 and d3 > d6 and d3 > d7 
         and d3 > d8 and d3 > d9 and d3 > d10 
         and d3 > d11 and d3 > d12 then d3
    
    ... more of the same until 
       else d12
      
       from myfile
    50,375 pointsBadges:
    report
  • philpl1jb
    Don't know why I just worked on a question from 2009 .. would like to retract my answer.
    50,375 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following