Get earliest record for document and return binary image field from another table

Transact-SQL
1

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 h.ORDNO
,h.DOCNAME
,d.RECID
,d.DOCDATE
,a.DOCUMENT
FROM (
SELECT RECID, ORDNO
,MIN(DATE) AS DOCDATE
FROM dochist
WHERE DOCTYPE = 35
AND DOCNAME = 'Delivery Confirmation'
GROUP BY ORDNO, RECID
) d
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:

image
image.png898×143 7.02 KB

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:

dochist
ORDNO varchar(12)
RECID char(32)
DOCNAME varchar(60)
DATE datetime
DOCTYPE numeric(3,0)

docarchive
RECID char(32)
PARID char(32) - link to the RECID in dochist
DOCUMENT image

Many thanks
Martyn

2

what is RecID?? Looks like that might be PK of the table in which case you would want to use min(RecID) in your query. Something like this:

SELECT h.ORDNO
,h.DOCNAME
,d.RECID
,d.DOCDATE
,a.DOCUMENT
FROM (
SELECT

min(RECID) RECID

, ORDNO
,MIN(DATE) AS DOCDATE
FROM dochist
WHERE DOCTYPE = 35
AND DOCNAME = 'Delivery Confirmation'
GROUP BY ORDNO
) d
INNER JOIN [ihdocarchive].[dbo].[DocArchive] a
ON a.PARID = d.RECID
INNER JOIN dochist h
ON h.RECID = a.PARID

ORDER BY h.ORDNO
1 Like
3

Hi, the recID is not the primary key on that table. The primary key is another field called recorder so I'll try that with your suggestion.

4

I've just tried exactly as per your suggestion and this seems to work so far as I've checked. So thank you - I have another similar one to do now, so will apply similar logic to that and hopefully it will work!

Kind regards
Martyn

5

Here's an alternative:

SELECT h.ORDNO
,h.DOCNAME
,d.RECID
,d.DOCDATE
,d.DOCUMENT
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ORDNO, RECID ORDER BY DATE) AS row_num
FROM dochist
) d
INNER JOIN [ihdocarchive].[dbo].[DocArchive] a
ON a.PARID = d.RECID
INNER JOIN dochist h
ON h.RECID = d.RECID
WHERE d.row_num = 1
ORDER BY h.ORDNO
6

@ScottPletcher,

Thank you - I will try this as well.

7

You need to add the criteria in the derived table to limit the documents returned to only those that meet the requirement and order the row number by DOCDATE to get the earliest version of that document.

SELECT h.ORDNO
,h.DOCNAME
,d.RECID
,d.DOCDATE
,d.DOCUMENT
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ORDNO, RECID ORDER BY DOCDATE) AS row_num
FROM dochist
WHERE DOCTYPE = 35
AND DOCNAME = 'Delivery Confirmation') d
INNER JOIN [ihdocarchive].[dbo].[DocArchive] a
ON a.PARID = d.RECID
INNER JOIN dochist h
ON h.RECID = d.RECID
WHERE d.row_num = 1
ORDER BY h.ORDNO
8

Sorry, I meant include the WHERE conditions in the inner query.

But there is no reason to re-join to the main table ("dochist") again. All the columns are available from the inner query. That is the point of using row_number.

9

I figured you did - and I didn't even look at the part where it is joining back to dochist again...I think that was already in the query.