filtering records by Max Date and including fields from that record

[Andy]

Hi,

I am trying to put together a Backup Matrix.

To demonstrate, I have a table that records a log of
completed backups. i.e

ServerID BackupDate TapeLabelDay TapeLabelWeek
------------------------------------------------------
SERVER1 20/11/03 THURSDAY WEEK 3
SERVER1 21/11/03 FRIDAY WEEK 3
SERVER1 22/11/03 SATURDAY WEEK 3
SERVER2 14/11/03 FRIDAY WEEK 3
SERVER2 15/11/03 SATURDAY WEEK 3
SERVER2 16/11/03 SUNDAY WEEK 3

.....and so on

What I am aiming for is a Backup Matrix that will simply
show the last backup date along with the Tape label for
that record, for each server. Thus, the result will be
something like......

SERVERID LASTBACKUPDATE TAPELABEL
---------------------------------------------------
SERVER1 22/11/03 SATURDAY WEEK 3
SERVER2 16/11/03 SUNDAY WEEK 3

Now, when I create my query and list just serverid and
date, the query works (the date has the Max function)
however once i add the label fields (these are
automatically "group by") the query then shows every
entry rather than the last date!

I don't want any grouping or functions on these fields, I
just want to show whatever tape label responds to the
record with the latest date. However becuase I put the
Max function in date, grouping options drop down for
every field and if I remove this group then the query
won't run.

I think the SQL of what I am trying to do is....

SELECT ServerID,Max([LastBackupDate]),DayLabel,WeekLabel;
FROM COMPLETEDBACKUPS;
GROUPBY ServerID;

However this comes up with an error, unless I make all
the other fields group by also!! But then that is when
it decides to list every record :-( So frustrating

If anyone knows where I am going wrong, any help would be
appreciated.

Thanks

Andy

 

[John Spencer (MVP)]

Try doing this with a subquery. Something like the UNTESTED SQL below.

SELECT ServerID, LastBackupDate,
DayLabel, WeekLabel
FROM COMPLETEDBACKUPS
WHERE LastBackupDate IN
(SELECT Max(Tmp.LastBackupDate)
FROM COMPLETEDBACKUPS As Tmp
WHERE Tmp.ServerID = CompletedBackups.ServerID)
ORDER BY ServerID

 

[Andy]

Thanks John, From the results shown, everything now
appears to be working. Just need to add other fields
now,and link with other table data, but that should be no
problem

Thanks again.

Andy

-----Original Message-----
Try doing this with a subquery. Something like the UNTESTED SQL below.

SELECT ServerID, LastBackupDate,
DayLabel, WeekLabel
FROM COMPLETEDBACKUPS
WHERE LastBackupDate IN
(SELECT Max(Tmp.LastBackupDate)
FROM COMPLETEDBACKUPS As Tmp
WHERE Tmp.ServerID = CompletedBackups.ServerID)
ORDER BY ServerID

Hi,

I am trying to put together a Backup Matrix.

To demonstrate, I have a table that records a log of
completed backups. i.e

ServerID BackupDate TapeLabelDay TapeLabelWeek
------------------------------------------------------
SERVER1 20/11/03 THURSDAY WEEK 3
SERVER1 21/11/03 FRIDAY WEEK 3
SERVER1 22/11/03 SATURDAY WEEK 3
SERVER2 14/11/03 FRIDAY WEEK 3
SERVER2 15/11/03 SATURDAY WEEK 3
SERVER2 16/11/03 SUNDAY WEEK 3

....and so on

What I am aiming for is a Backup Matrix that will simply
show the last backup date along with the Tape label for
that record, for each server. Thus, the result will be
something like......

SERVERID LASTBACKUPDATE TAPELABEL
---------------------------------------------------
SERVER1 22/11/03 SATURDAY WEEK 3
SERVER2 16/11/03 SUNDAY WEEK 3

Now, when I create my query and list just serverid and
date, the query works (the date has the Max function)
however once i add the label fields (these are
automatically "group by") the query then shows every
entry rather than the last date!

I don't want any grouping or functions on these fields, I
just want to show whatever tape label responds to the
record with the latest date. However becuase I put the
Max function in date, grouping options drop down for
every field and if I remove this group then the query
won't run.

I think the SQL of what I am trying to do is....

SELECT ServerID,Max ([LastBackupDate]),DayLabel,WeekLabel;
FROM COMPLETEDBACKUPS;
GROUPBY ServerID;

However this comes up with an error, unless I make all
the other fields group by also!! But then that is when
it decides to list every record :-( So frustrating

If anyone knows where I am going wrong, any help would be
appreciated.

Thanks

Andy

.