I have two tables, dochist and docarchive which contain document details for an order. I need to get the earliest Delivery Confirmation document (DOCTYPE = 35 and DOCNAME = 'Delivery Confirmation') for an order number from the dochist table. The problem is that it is possible for there to be one than one Delivery Confirmation for an order, but I am only interested in the earliest record. Once I have that, I need to fetch the binary image from the docarchive table.
My query code is below:
SELECT RECID, ORDNO
,MIN(DATE) AS DOCDATE
WHERE DOCTYPE = 35
AND DOCNAME = 'Delivery Confirmation'
GROUP BY ORDNO, RECID
INNER JOIN [ihdocarchive].[dbo].[DocArchive] a
ON a.PARID = d.RECID
INNER JOIN dochist h
ON h.RECID = a.PARID
ORDER BY h.ORDNO`
But this returns duplicates where there is more than one Delivery Confirmation for an order number:
How can I get it to return only the document image information for the oldest Delivery Confirmation?
The fields in the table are as follows:
PARID char(32) - link to the RECID in dochist