Get Top1 or Max of subquery
2

Question

Get Top1 or Max of subquery

waubain (TechnicalUser)

(OP)

3 Jan 19 16:56

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

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...

Andrzejek (Programmer) · Answer 1 · 2019-01-03 17:17:28

How does the table tblBookEdition relate to tblRevisionLog table?
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.

waubain (TechnicalUser) · Answer 2 · 2019-01-03 17:29:10

Andy,

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...

Andrzejek (Programmer) · Answer 3 · 2019-01-03 18:48:29

I would try something like this (not tested):

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.

waubain (TechnicalUser) · Answer 4 · 2019-01-03 20:05:15

Andy,

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.

Andrzejek (Programmer) · Answer 5 · 2019-01-03 20:53:45

Try to play with Max(), maybe....:

Select Max(BookEdition)
From tblBookEdition
Where EditionPubDate < #12/16/1999#

---- Andy

There is a great need for a sarcasm font.

waubain (TechnicalUser) · Answer 6 · 2019-01-03 23:25:49

This SQL worked. Thanks for the ideas.

CODE --> SQL

SELECT tblRevisionLog.pk_RevisionLogID, tblRevisionLog.DateTimeCreated, Min(Edition) AS BookEdition
FROM dimBookEdition, tblRevisionLog
WHERE (([dimBookEdition].[EditionPubDate]>[tblRevisionLog].[DateTimeCreated]))
GROUP BY tblRevisionLog.pk_RevisionLogID, tblRevisionLog.DateTimeCreated;

dhookom (Programmer) · Answer 7 · 2019-01-03 23:32:14

Try this SQL:

CODE --> Sql

SELECT tblRevisionLog.pk_RevisionLogID, tblRevisionLog.DateTimeCreated, 
(SELECT TOP 1 BookEdition
 FROM dimBookEdition
 WHERE EditionPubDate >= DateTimeCreated ORDER BY EditionPubDate) AS BookEdition
FROM tblRevisionLog;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

dhookom (Programmer) · Answer 8 · 2019-01-03 23:34:06

I think our postings crossed in compositionland.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

waubain (TechnicalUser) · Answer 9 · 2019-01-04 04:14:18

Duane,
Yours worked also. Thanks for providing an alternate solution.

Come Join Us!

Posting Guidelines

Get Top1 or Max of subquery
2