Nested Max() Statments

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table where each record has a Date/Time stamp. Each the user retrieves and then saves data a new record is created. My problem is now in retrieveing these records by using the Max() statement in a nested Query. I have several tables but one is causing me the most greif as it has one extra field Note_ID which means i need to retrieve data base on a Case number as well as a note number.

Here is the query that i use on the other tables to return data

SELECT Agent, Case, DT_Stamp, Field_1, Field_2, Field_3, User
FROM Cases INNER JOIN (SELECT Case, Max(DT_Stamp) as MaxDate FROM Cases GROUP BY Case) AS MaxDS
ON (Cases.Case = MaxDS.Case) AND (Cases.DT_Stamp = MaxDS.MaxDate);

This works fine and brings back the latest record for each case based on the Date/Time.

The table giving me the problems has one extra field called Note_ID, this allows multiple notes for each Case. I have tried the query below but it only returns the one record for each Note_ID even though the particuler Note_ID appears for several different Cases.

SELECT Agent, Case, DT_Stamp, Note_ID, Field_1
FROM Notes INNER JOIN (SELECT Note_ID, Max(DT_Stamp) as MaxDate FROM Notes GROUP BY Note_ID) AS MaxDS
ON (Notes .Note_ID = MaxDS.Note_ID) AND (Notes .DT_Stamp = MaxDS.MaxDate);


I can't seem to find the right SQL statment that will give me the maximum Date/Time for each Note_ID for each case. A sample of the data is listed below. Can anybody help with suggestion on how i can achieve this?

Thanks in Advance

Agent Case DT_Stamp Note_ID Field_1
a 1 03/03/2003 14:25:00 1 45
a 1 03/03/2003 14:40:00 1 46
a 1 03/03/2003 14:45:00 1 48
a 1 03/03/2003 14:25:00 2 12
a 1 03/03/2003 14:35:00 2 18
b 5 03/03/2003 18:25:00 1 4
a 5 05/03/2003 12:25:00 2 47
c 6 08/03/2003 10:25:00 1 23
 
Hi Shaun,

It looks to me (if I understand correctly)
that you just need to add Case to your
subquery.

Using the method you have started with:

SELECT Agent, Case, DT_Stamp, Note_ID, Field_1
FROM Notes INNER JOIN
(SELECT Case, Note_ID, Max(DT_Stamp) as MaxDate
FROM Notes
GROUP BY Case, Note_ID) AS MaxDS
ON (Notes .Note_ID = MaxDS.Note_ID)
AND (Notes .DT_Stamp = MaxDS.MaxDate)
AND (Notes .Case= MaxDS.Case);

Please respond back if I have misunderstood.

Good luck,

Gary Walter


Shaun Goudy said:
I have a table where each record has a Date/Time stamp. Each the user
Click to expand...
retrieves and then saves data a new record is created. My problem is now in
retrieveing these records by using the Max() statement in a nested Query. I
have several tables but one is causing me the most greif as it has one extra
field Note_ID which means i need to retrieve data base on a Case number as
well as a note number.
Here is the query that i use on the other tables to return data

SELECT Agent, Case, DT_Stamp, Field_1, Field_2, Field_3, User
FROM Cases INNER JOIN (SELECT Case, Max(DT_Stamp) as MaxDate FROM Cases GROUP BY Case) AS MaxDS
ON (Cases.Case = MaxDS.Case) AND (Cases.DT_Stamp = MaxDS.MaxDate);

This works fine and brings back the latest record for each case based on the Date/Time.

The table giving me the problems has one extra field called Note_ID, this
Click to expand...
allows multiple notes for each Case. I have tried the query below but it
only returns the one record for each Note_ID even though the particuler
Note_ID appears for several different Cases.
SELECT Agent, Case, DT_Stamp, Note_ID, Field_1
FROM Notes INNER JOIN (SELECT Note_ID, Max(DT_Stamp) as MaxDate FROM Notes GROUP BY Note_ID) AS MaxDS
ON (Notes .Note_ID = MaxDS.Note_ID) AND (Notes .DT_Stamp = MaxDS.MaxDate);


I can't seem to find the right SQL statment that will give me the maximum
Click to expand...
Date/Time for each Note_ID for each case. A sample of the data is listed
below. Can anybody help with suggestion on how i can achieve this?
 
Back
Top