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.
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.
Code:
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
Last edited: