Get Top1 or Max of subquery
Get Top1 or Max of subquery
(OP)
I am working with a db that tracks revisions to a book. Revisions are captured in the Revisions table with a timestamp. I need to show in a form which Book Edition the revision affected (which is always the next published edition).
The 2 tables with data that look like this:
dimBookEdition
tblRevisionLog
The desired output
This query will give me the list, but I cannot figure out how to get the MAX DiffPubDate for each group of revision ID's or Row 1.
Thank you for any help.
The 2 tables with data that look like this:
dimBookEdition
CODE -->
pk_BookEditionID BookEdition EditionPubDate 1 1st Edition 3/1/1997 2 2nd Edition 1/15/2000 3 3rd Edition 11/3/2003 4 4th Edition 1/1/2008 5 5th Edition 11/28/2012 6 6th Edition 1/29/2018 7 7th Edition 12/31/2023
tblRevisionLog
CODE -->
pk_RevisionLogID DateTimeCreated 36 01/16/1996 37 02/10/1997 38 12/16/1999 39 06/26/2005 40 04/25/2009 41 12/06/2018
The desired output
CODE -->
pk_RevisionLogID DateTimeCreated BookEdition 36 01/16/1996 1st Edition 37 02/10/1997 1st Edition 38 12/16/1999 2nd Edition 39 06/26/2005 4th Edition 40 04/25/2009 5th Edition 41 12/06/2018 7th Edition
This query will give me the list, but I cannot figure out how to get the MAX DiffPubDate for each group of revision ID's or Row 1.
CODE --> SQL
SELECT pk_RevisionLogID, DateTimeCreated, Edition, DateDiff("d",[dimBookEdition].[EditionPubDate],[tblRevisionLog].[DateTimeCreated]) AS DiffPubDate FROM dimBookEdition, tblRevisionLog ORDER BY pk_RevisionLogID, DateDiff("d",[dimBookEdition].[EditionPubDate],[tblRevisionLog].[DateTimeCreated]) DESC
Thank you for any help.
You don't know what you don't know...
RE: Get Top1 or Max of subquery
How do you know which record(s) from one table relate to which record(s) from another?
---- Andy
There is a great need for a sarcasm font.
RE: Get Top1 or Max of subquery
The tables are not related to each other.
Basically I am trying to find where DateTimeCreated falls between which 2 EditionPubDate dates. So in the case of tblRevisionLog.pk_RevisionLogID #38 date falls between the 1st and 2nd Edition, so the revision appeared in the 2nd Edition.
I hope this makes sense.
You don't know what you don't know...
RE: Get Top1 or Max of subquery
Select pk_RevisionLogID, DateTimeCreated,
(Select Top 1 BookEdition
From tblBookEdition
Where EditionPubDate < tblRevisionLog.DateTimeCreated
Order By DateTimeCreated Desc) as BookEdition
From tblRevisionLog
Order By 1
but first I would make sure the Blue part works with the tblRevisionLog.DateTimeCreated hard-coded with the date of 12/16/1999 (following your example of records 38)
---- Andy
There is a great need for a sarcasm font.
RE: Get Top1 or Max of subquery
The Blue part does not work, even after hard coding. It does not choose a Top 1, but gives all Editions < the hard coded date. I did change the < to > as I need the next Edition after the DateTimeCreated. but the Top 1 did not give the desired result. I will keep trying, Thanks for the ideas.
RE: Get Top1 or Max of subquery
Select Max(BookEdition)
From tblBookEdition
Where EditionPubDate < #12/16/1999#
---- Andy
There is a great need for a sarcasm font.
RE: Get Top1 or Max of subquery
CODE --> SQL
RE: Get Top1 or Max of subquery
CODE --> Sql
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Get Top1 or Max of subquery
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Get Top1 or Max of subquery
Yours worked also. Thanks for providing an alternate solution.