Grouped MAX Records?

M

Mark Stephenson

I have a table that contains quite a few field, the ones I am interested in
are as follows;

REL_ID Relationship ID (table
key)
RTY_CODE Text, the criteria needs to
be 'SCP' for this.
REL_SOURCE_PER_GRO_ID This is my client identifyer, there
can be
duplicates for this
field in the table
REL_START_DATE Date Field (need the max of this)
REL_TO_PER_GRO_ID This is who the relationship is
too...

The table stores relationships, I need the Most currently created (Max REL
START DATE) Per Client And i need to display the START DATE, CLIENT ID and
REL TO PER GRO ID.

I have tried searching these forums but I can seem to apply any of the help
found to me query. I have not used nested selects before so any help would
be great.

Many thanks,


Mark Stephenson
 
M

Mark Stephenson

Thanks Alan... Have been experimenting with LAST in the totals row... Not
sure if its givin me correct data, on checking now.. But I am not getting any
duplication...

SELECT Last(CFLIVE_O_RELATIONSHIPS.REL_ID) AS LastOfREL_ID,
CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID,
Max(CFLIVE_O_RELATIONSHIPS.REL_START_DATE) AS MaxOfREL_START_DATE,
Last(CFLIVE_O_RELATIONSHIPS.REL_TO_PER_GRO_ID) AS LastOfREL_TO_PER_GRO_ID
FROM CFLIVE_O_RELATIONSHIPS
WHERE (((CFLIVE_O_RELATIONSHIPS.REL_RTY_CODE)="SCP"))
GROUP BY CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID;

I am just about to check out your link...

Thanks,

Mark Stephenson
 
A

Allen Browne

Try Max instead of Last.

"Max" means the highest value. For a date field, that means the latest date.

"Last" means read all the records until you come to the last match - and
that's defined by however they happen to be sorted - so it's probably not
the result you want.
 
M

Mark Stephenson

Allen,

I am now picking up what I think is the correct start date (Max), but still
cant figure out how to get the client relationship name for the record with
the max start date!

I am using;

SELECT Max(CFLIVE_O_RELATIONSHIPS.REL_ID) AS MaxOfREL_ID,
CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID,
Max(CFLIVE_O_RELATIONSHIPS.REL_START_DATE) AS MaxOfREL_START_DATE,
Max(CFLIVE_O_RELATIONSHIPS.REL_TO_PER_GRO_ID) AS MaxOfREL_TO_PER_GRO_ID,
Max(IIf([REL_END_DATE] Is Null,"Y","N")) AS [Open]
FROM CFLIVE_O_RELATIONSHIPS
WHERE (((CFLIVE_O_RELATIONSHIPS.REL_RTY_CODE)="SCP"))
GROUP BY CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID;


Any ideas? I think I have to nest a select somewhere, but am unsure where
or how!

Thanks,
 
A

Allen Browne

Try something like this:

SELECT CFLIVE_O_RELATIONSHIPS.*
FROM CFLIVE_O_RELATIONSHIPS
WHERE CFLIVE_O_RELATIONSHIPS.REL_ID =
( SELECT TOP 1 Dupe.REL_ID AS FirstOfRelID
FROM CFLIVE_O_RELATIONSHIPS AS Dupe
WHERE (Dupe.REL_SOURCE_PER_GRO_ID =
CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID)
AND (Dupe.REL_RTY_CODE = "SCP")
ORDER BY Dupe.REL_START_DATE DESC, Dupe.REL_ID DESC );


The subquery returns the primary key value of the record where the client
matches and the code is SCP. The subquery is sorted by the date in
descending order, it will be return the primary key of the most recent
matching record. To prevent the subquery failing if there's more than one
for the same date, we told it to pick the one with the highest REL_ID value
(2nd field in the ORDER BY clause).

Now that the WHERE clause returns only the primary key value for the most
recent record for the client, you should be able to get the rest of the
field in the main query.

Not sure that's the most efficent solution, but it should work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Allen,

I am now picking up what I think is the correct start date (Max), but
still
cant figure out how to get the client relationship name for the record
with
the max start date!

I am using;

SELECT Max(CFLIVE_O_RELATIONSHIPS.REL_ID) AS MaxOfREL_ID,
CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID,
Max(CFLIVE_O_RELATIONSHIPS.REL_START_DATE) AS MaxOfREL_START_DATE,
Max(CFLIVE_O_RELATIONSHIPS.REL_TO_PER_GRO_ID) AS MaxOfREL_TO_PER_GRO_ID,
Max(IIf([REL_END_DATE] Is Null,"Y","N")) AS [Open]
FROM CFLIVE_O_RELATIONSHIPS
WHERE (((CFLIVE_O_RELATIONSHIPS.REL_RTY_CODE)="SCP"))
GROUP BY CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID;


Any ideas? I think I have to nest a select somewhere, but am unsure where
or how!

Thanks,


Allen Browne said:
Try Max instead of Last.

"Max" means the highest value. For a date field, that means the latest
date.

"Last" means read all the records until you come to the last match - and
that's defined by however they happen to be sorted - so it's probably not
the result you want.
Click to expand...
Click to expand...
 
M

Mark Stephenson

Allen,

Thanks for your continued help... I see what is trying to be done with the
code you gave me;

SELECT CFLIVE_O_RELATIONSHIPS.*
FROM CFLIVE_O_RELATIONSHIPS
WHERE CFLIVE_O_RELATIONSHIPS.REL_ID =
( SELECT TOP 1 Dupe.REL_ID AS FirstOfRelID
FROM CFLIVE_O_RELATIONSHIPS AS Dupe
WHERE (Dupe.REL_SOURCE_PER_GRO_ID =
CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID)
AND (Dupe.REL_RTY_CODE = "SCP")
ORDER BY Dupe.REL_START_DATE DESC, Dupe.REL_ID DESC );

But when I try to run it, the egg timer comes up and thats all that
happens... I have waited for about 20 minutes, then CTRL+BREAK...

I was also trying something else... I created a QRY that pulled just SCP
records from the table, then used example 4 from the link you gave me to try
and get it to work, I still had duplicates.. I really dont get it!!

Select Q.Client, Q.REL_ID, Q.Date
FROM REL_SCP as Q INNER JOIN (SELECT Client, Max(Date) As S From REL_SCP
GROUP BY Client) As T ON (Q.Date = T.S) AND (Q.Client = T.Client);

Where REL_SCP is the new table with 3 Aliased fields Client
(REL_SOURCE_PER_GRO_ID), REL_ID, Date(REL_START_DATE)

Not sure which avenue to persue... as I cant seem to get either to work!!
 
A

Allen Browne

The duplicates are probably coming where a client has more than one entry
for the same date.

Here's another approach.

Okay: this query should return 1 record for each client, along with their
most recent date:

SELECT CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID,
Max(CFLIVE_O_RELATIONSHIPS.REL_START_DATE) AS MaxOfREL_START_DATE
FROM CFLIVE_O_RELATIONSHIPS
WHERE CFLIVE_O_RELATIONSHIPS.REL_RTY_CODE = "SCP"
GROUP BY CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID;

Save that query with the name "CFLiveMaxDate", and test. Runs okay?

Now try this one, which reads from the first, and gets the most recent
primary key for the matching record:
SELECT CFLiveMaxDate.REL_SOURCE_PER_GRO_ID,
CFLiveMaxDate.MaxOfREL_START_DATE, Max(CFLIVE_O_RELATIONSHIPS.REL_ID) AS
MaxOfREL_ID
FROM CFLiveMaxDate INNER JOIN CFLIVE_O_RELATIONSHIPS ON
(CFLiveMaxDate.MaxOfREL_START_DATE = CFLIVE_O_RELATIONSHIPS.REL_START_DATE)
AND (CFLiveMaxDate.REL_SOURCE_PER_GRO_ID =
CFLIVE_O_RELATIONSHIPS.REL_SOURCE_PER_GRO_ID)
GROUP BY CFLiveMaxDate.REL_SOURCE_PER_GRO_ID,
CFLiveMaxDate.MaxOfREL_START_DATE;

If that works with acceptable performance, you should then be able to join
that query to another on the primary key, to get everything.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
M

Mark Stephenson

Allen,

I have tried things that look pretty much the same as these 2 before, and it
just sits there!!! as is your code (1st query runs fine) ... The status bar
across the bottom gets to 1 bar then doesnt move!!

I really appreciate your continued assistance with this... I realise HOW
this should work... I just dont understand why?!!
 
A

Allen Browne

Not sure what else to suggest, Mark.

Presumably you have an index on the date field?
It may be a matter of fiddling with ideas until you get the optimized one.

Worst case, you have a query that is returning those 2 things quickly, so
you could write that to a temp table (INSERT INTO ...), and then build from
there?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
 
M

Mark Stephenson

Allen,

Thanks for all your help... I think to be honest the problem is Access!!! I
tried recreating the tables etc will dummy data, only a few hundred rows and
everything works!!! Must have something to do with the amount of data in the
Relationships table (About 200000 rows and in total about 30 fields!!) That
combined with draggin the info via ODBC and using Citrix to access
MsAccess!!!

At least I know that I am sane!!!

Many thanks for all your help
 
Top