Pulling only Newest Child Record

[nickh]

I have a one-to-many database, with the parent record holding the business
name, phone, address, etc. The child database contains multiple activity
records that are timestamped each instance a communication is made with the
company. The child records have phone as the foreign key linking back to the
parent, datestamp, result, and notes. I want to write a SQL query (hopefully
1 single query), that will return the business name & phone from the parent
record, along with the datestamp, result, and notes from the linked child
record but only for the last activity record (i.e., the activity record with
the latest datestamp). I cannot figure out how to do this!! Do I use the
Last aggregation function? A subquery and top 1 with descending order?
Help!! This is driving me crazy!

 

[Rick Brandt]

I have a one-to-many database, with the parent record holding the
business name, phone, address, etc. The child database contains
multiple activity records that are timestamped each instance a
communication is made with the company. The child records have phone
as the foreign key linking back to the parent, datestamp, result, and
notes. I want to write a SQL query (hopefully 1 single query), that
will return the business name & phone from the parent record, along
with the datestamp, result, and notes from the linked child record
but only for the last activity record (i.e., the activity record with
the latest datestamp). I cannot figure out how to do this!! Do I
use the Last aggregation function? A subquery and top 1 with
descending order? Help!! This is driving me crazy!

Sort Descending on the datestamp field and then set the Top Values of the query
to 1.

 

[nickh]

Rick, okay I am using a correlated subquery, and using the Top 1 Descending
in the subquery. Here is my sql. Does this look correct? co is the parent
table (co=company). ac is the child table (ac=activities).
AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID is an autonumber field in the child
that should mirror the datestamp (i.e., the highest
AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID will be the newest record) [FYI, this
goes back to my post a couple weeks ago about multi-field primary keys that
started a big argument on this group!!]. This appears to return the correct
results, but if I add "order by co.[company phone]" it takes a long time to
sort, which is weird since co.[company phone] is the primary key of co table,
and it ought to retrieve in correct natural order to begin with !?!

SELECT co.[company phone],AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID, AC.RESULT,
AC.RESULTTYPE, AC.[BROADCAST DATE] & " " & AC.[BROADCAST TIME] AS Expr1
FROM WORKING_UNIQUE_COMPANY_TABLE AS co INNER JOIN
WORKING_UNIQUE_ACTIVITIES_TABLE AS AC ON co.[COMPANY PHONE] = AC.NUMBER
where AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID = (select top 1
AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID
from WORKING_UNIQUE_ACTIVITIES_TABLE AC
where ac.number = co.[company phone]
order by AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID desc);

 

[Rick Brandt]

Rick, okay I am using a correlated subquery, and using the Top 1
Descending in the subquery. Here is my sql. Does this look correct?
co is the parent table (co=company). ac is the child table
(ac=activities). AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID is an
autonumber field in the child that should mirror the datestamp (i.e.,
the highest AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID will be the newest
record) [FYI, this goes back to my post a couple weeks ago about
multi-field primary keys that started a big argument on this
group!!]. This appears to return the correct results, but if I add
"order by co.[company phone]" it takes a long time to sort, which is
weird since co.[company phone] is the primary key of co table, and it
ought to retrieve in correct natural order to begin with !?!

SELECT co.[company phone],AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID,
AC.RESULT, AC.RESULTTYPE, AC.[BROADCAST DATE] & " " & AC.[BROADCAST
TIME] AS Expr1
FROM WORKING_UNIQUE_COMPANY_TABLE AS co INNER JOIN
WORKING_UNIQUE_ACTIVITIES_TABLE AS AC ON co.[COMPANY PHONE] =
AC.NUMBER where AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID = (select top 1
AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID
from WORKING_UNIQUE_ACTIVITIES_TABLE AC
where ac.number = co.[company phone]
order by AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID desc);

Looks fine to me. Actually wasn't thinking of a subquery before. With a
subquery SELECT Max(AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID) ... might be faster.

 

[nickh]

How would you do it without a subquery? If I use the Top 1, I get just 1
single record returned. I need the 1 record for each phone# (and there are
313,000+ phone #'s in the co table!)

Rick, okay I am using a correlated subquery, and using the Top 1
Descending in the subquery. Here is my sql. Does this look correct?
co is the parent table (co=company). ac is the child table
(ac=activities). AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID is an
autonumber field in the child that should mirror the datestamp (i.e.,
the highest AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID will be the newest
record) [FYI, this goes back to my post a couple weeks ago about
multi-field primary keys that started a big argument on this
group!!]. This appears to return the correct results, but if I add
"order by co.[company phone]" it takes a long time to sort, which is
weird since co.[company phone] is the primary key of co table, and it
ought to retrieve in correct natural order to begin with !?!

SELECT co.[company phone],AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID,
AC.RESULT, AC.RESULTTYPE, AC.[BROADCAST DATE] & " " & AC.[BROADCAST
TIME] AS Expr1
FROM WORKING_UNIQUE_COMPANY_TABLE AS co INNER JOIN
WORKING_UNIQUE_ACTIVITIES_TABLE AS AC ON co.[COMPANY PHONE] =
AC.NUMBER where AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID = (select top 1
AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID
from WORKING_UNIQUE_ACTIVITIES_TABLE AC
where ac.number = co.[company phone]
order by AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID desc);

Looks fine to me. Actually wasn't thinking of a subquery before. With a
subquery SELECT Max(AC.WORKING_UNIQUE_ACTIVITIES_TABLE_ID) ... might be faster.

 

[Rick Brandt]

How would you do it without a subquery? If I use the Top 1, I get
just 1 single record returned. I need the 1 record for each phone#
(and there are 313,000+ phone #'s in the co table!)

I was originally thinking you were using a form with a subform and only wanted
to see the newest child in the subform. A SELECT TOP 1 for just the subform's
query would do that. One query to pull multiple parent records with just the
newest child per-parent would need a subquery as you are doing.

 

[nickh]

Cool!

Your Max() idea did work much faster, AND the phone#'s are now showing in
their correct natural order!! Thnx for that tip.