List Highest/Top Document number under two conditions

[sxschech]

This query seems to work and not asking how to get it working. Rather, checking if this is the right approach or is there another preferable method.

Have a list of document numbers and would like to display the highest one, however if there are documents that contain a title of TBD, would like to see the highest one containing TBD in the title in addition to the highest overall. Reason being - sometimes a document number is assigned and then turns out is not needed at that moment, so the title is given TBD as a place holder and then that document number can be used for a future document. In the dataset, I noticed that there was a document with a TBD from 2018, and since that one hasn't been used, I am assuming they are not going to use it, so I added criteria to only list the top TBD in the last 6 months.

SELECT Docs.DocumentNo, Docs.DocumentTitle, Docs.Revision, Docs.DateModified
FROM Docs
INNER JOIN (
SELECT TOP 1 Docs.DocumentNo
FROM Docs
WHERE (Docs.DocumentNo Like "B*" AND DocumentTitle<>'TBD')
ORDER BY 1 DESC
UNION
SELECT TOP 1 Docs.DocumentNo
FROM Docs
WHERE (Docs.DocumentNo Like "B*" AND DocumentTitle='TBD')
AND Docs.DateModified>DateAdd("m",-6,now())
ORDER BY 1) AS Top1
ON Docs.DocumentNo=Top1.DocumentNo

 

[theDBguy]

Hi. I was thinking maybe you could compare that with a TOP N Per Group query, but I can't help you create it without some sample data to play with. Just a thought...

 

[sxschech]

Hi theDBGuy, appreciate you taking a look. Here is a list of sample data slightly edited so as not to use the actual numbers and titles. If needs to be in another format or as attachment, let me know. I copied and pasted from Access to Excel, edited it and then copy pasted in here. I included a helper col at the end that contains only the numeric portion. Of course there are documentno that begin with other letters, so cannot rely on that numeric col for groupings.

FileType	DocumentNo	DocumentTitle	Revision	DocumentStatus	UploadingOrganization	DateModified	RevisionDate	DocumentType	FileSize	Printed	DateAppended	DateUpdated	DocDate	DocNo
doc	BB-DB-LTR-000603	TBD	0	Not Yet Submitted	BB	11-Sep-18		Letter	11 KB	No	12-Oct-18	16-Dec-19	11-Sep-18	603
pdf	BB-DB-LTR-000855	2019.12.09-090-LTR-000855-BB-DB-December 3, 2019 Workshop-Rev0	0	For Review	BB	12/9/2019 17:49		Letter	86.8 KB	No	12/16/2019 7:54	12/16/2019 7:54	12/9/2019	855
pdf	BB-DB-LTR-000856	2019.12.13-090-LTR-000856-BB-DB-Response to Rev. 1 Machine-Rev0	0	For Review	BB	12/13/2019 18:31		Letter	35.3 KB	No	12/16/2019 7:54	12/16/2019 7:54	12/13/2019	856
pdf	BB-DB-LTR-000857	2019.12.13-090-LTR-000857-BB-DB-Response to Letter 435 -Rev0	0	For Review	BB	12/13/2019 19:01		Letter	38.3 KB	No	12/16/2019 7:54	12/16/2019 7:54	12/13/2019	857
doc	BB-DB-LTR-000858	TBD	0	Not Yet Submitted	BB	12/13/2019 19:23		Letter	32.9 KB	No	12/16/2019 7:54	12/16/2019 7:54	12/13/2019	858
pdf	BB-DB-LTR-000859	2019.12.13-090-LTR-000859-BB-DB-Response to Letter 431 Permit-Rev0	0	For Review	BB	12/13/2019 19:29		Letter	45.6 KB	No	12/16/2019 7:54	12/16/2019 7:54	12/13/2019	859

 

[theDBguy]

Hi. Thanks. Can you post the Excel file to help me with slicing that data into columns? I couldn't tell where Document Title ends and Revision begins.

 

[sxschech]

Sorry about that, it seemed to have been tab delim, but maybe lost the tabs in pasting?

 

[theDBguy]

Sorry about that, it seemed to have been tab delim, but maybe lost the tabs in pasting?

Hi. Thanks. That helped a lot. So, I imported the data into a table and used your query to get the following result. Please confirm the result is correct, so I can try to see if I can create a new query with the same result.

 

[sxschech]

That looks correct.

 

[theDBguy]

That looks correct.

Okay, thanks. Here's one way I used to get the same result. There may be other ways too.

SELECT Docs.DocumentNo, Docs.DocumentTitle, Docs.Revision, Docs.DateModified
FROM Docs
INNER JOIN (SELECT Max(Docs.DocumentNo) AS MaxOfDocumentNo
FROM Docs
WHERE Docs.DateModified>DateAdd("m",-6,Date())
GROUP BY IIf([DocumentTitle]='tbd',1,2)) AS SQ
ON Docs.DocumentNo=SQ.MaxofDocumentNo

 

[sxschech]

After adding back the Criteria for the document number, I think it is providing the same result. Thanks. Is the advantage of your sql that it is one subquery rather than a union?

SELECT Docs.DocumentNo, Docs.DocumentTitle, Docs.Revision, Docs.DateModified
FROM Docs
INNER JOIN (SELECT Max(Docs.DocumentNo) AS MaxOfDocumentNo
FROM Docs
WHERE Docs.DateModified>DateAdd("m",-6,Date())
[B][COLOR="Red"]AND Docs.DocumentNo Like 'B*'[/COLOR][/B]
GROUP BY IIf([DocumentTitle]='tbd',1,2)) AS SQ
ON Docs.DocumentNo=SQ.MaxofDocumentNo

 

[theDBguy]

After adding back the Criteria for the document number, I think it is providing the same result. Thanks. Is the advantage of your sql that it is one subquery rather than a union?

SELECT Docs.DocumentNo, Docs.DocumentTitle, Docs.Revision, Docs.DateModified
FROM Docs
INNER JOIN (SELECT Max(Docs.DocumentNo) AS MaxOfDocumentNo
FROM Docs
WHERE Docs.DateModified>DateAdd("m",-6,Date())
[B][COLOR=Red]AND Docs.DocumentNo Like 'B*'[/COLOR][/B]
GROUP BY IIf([DocumentTitle]='tbd',1,2)) AS SQ
ON Docs.DocumentNo=SQ.MaxofDocumentNo

Hi. Sorry about that. I didn't think to add the criteria because it didn't matter in the sample data you provided. Glad to hear you got it to work. Yes, that's about the only difference (UNION vs Subquery), but I'm not sure if there's an advantage or not. Like I said, I was thinking of another way to do it, but it's probably just going to make it more complicated than that. Cheers!