DB2 SQL - Inner Join get last row

Question

I am trying to get the last record from table @POA with the same PO # and Line # in table HPOL07. The records in @POA have a sequence number. So I am trying to get the record with the last sequence number. I am not sure how to do this?

EXEC SQL Declare RSCURSOR cursor for
SELECT HPOL07.*, @POA.*
FROM HPOL07
INNER JOIN @POA ON PORD = @POA.POAPON  AND PLINE = @POA.POALNO
WHERE PORD = :NBR AND PID <> 'PZ';

EXEC SQL  Open RSCURSOR;

EXEC SQL SET RESULT SETS Cursor RSCURSOR;

Andrew · Accepted Answer · 2018-09-06 18:00:31Z

0

Won't swear this works on DB2 400, but I think it will.

    SELECT HPOL07.*, @POA.*
    FROM HPOL07
    INNER JOIN @POA ON PORD = @POA.POAPON  AND PLINE = @POA.POALNO
    WHERE PORD = :NBR AND PID <> 'PZ'
    ORDER BY @POA.<SEQUENCE NUMBER> DESC
FETCH FIRST 1 ROWS ONLY

edited Sep 6, 2018 at 18:00

answered Sep 6, 2018 at 17:50

Andrew

8,8933 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tretiak Over a year ago

The ORDER BY and FETCH you added, are they for the HPOL07 table or @POA table?

Andrew Over a year ago

The fetch is for the result of the entire query. For the order by is for whatever table.column is your sequence number.

Tretiak Over a year ago

Thank you Andrew, it worked. I am still confused with the ORDER BY and FETCH. Would you mind clarifying? The ORDER BY would work with both tables? What about the FETCH. How does it know what table to use?

Andrew Over a year ago

Both the order by and fetch apply to the result set of the query. Order by sorts by the specified column in the result set, which in turn allows the fetch first N rows to pick the desired rows.

Tretiak Over a year ago

Andrew, after further testing this only bring one result sets. I need to return multiple records from table HPOL07 with one result from the @POA table. Do I need to do a subquery?

|

ChrisHiebert · Accepted Answer · 2018-09-28 14:25:44Z

0

This query can be used to get all records from HPOL07 and match to one record in @POA

SELECT HPOL07.*, @POA.*
FROM HPOL07

CROSS JOIN LATERAL (
SELECT @POA.*
FROM @POA
WHERE PORD = @POA.POAPON  AND PLINE = @POA.POALNO
ORDER BY @POA.<SEQUENCE NUMBER> DESC
FETCH FIRST 1 ROWS ONLY
) @POA 

WHERE PORD = :NBR AND PID <> 'PZ'

answered Sep 28, 2018 at 14:25

ChrisHiebert

2002 silver badges5 bronze badges

Collectives™ on Stack Overflow

DB2 SQL - Inner Join get last row

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related