CODEWITHSUNDEEP
sqlselectcountdb2limit
whatismyname123
whatismyname123

Reputation: 159

DB2 - Limit fetched rows in table by selecting row count from other table

I am trying to get row count in Table_A, save it in a variable and fetch the same row count in Table_B.

CREATE VARIABLE myvar INTEGER;
SET myvar = (SELECT count (*) FROM Table_A)

SELECT * from Table_B
fetch first {mywar} rows ONLY

DROP VARIABLE myvar;

How can I achieve this in DB2? Is it better in this case to use Limit? Can this be achieved without using variables?

Upvotes: 0

Views: 618

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

You could do e.g.

SELECT * FROM
( SELECT *, ROW_NUMBER() OVER() AS RN from Table_B)
WHERE RN <= (SELECT COUNT(*) FROM Table_A)

Upvotes: 1

Related Questions