How to write optimised SELECT SQL query or Java batch-CodePudding

I have an orders table as shown below.

     ---------- ------------- --------- -------------- 
    | Name     | orderDate   | memberId| OrderId      |
     ---------- ------------- --------- -------------- 
    |      Tom |  01-01-2023 |     ABC | 111          |
    |     Dick |  01-01-2023 |     XYZ | 222          |
    |    Harry |  01-01-2023 |     PQR | 666          |
    |     Dick |  01-01-2023 |     XYZ | 222          |
    |      Tom |  02-01-2023 |     ABC | 111          |
    |    Harry |  03-01-2023 |     PQR | 666          |
    |     Dick |  03-01-2023 |     XYZ | 222          |
    |      Tom |  04-01-2023 |     ABC | 111          |
    |     Dick |  06-01-2023 |     XYZ | 222          |
    |     Dick |  07-01-2023 |     XYZ | 222          |
    |    Harry |  04-01-2023 |     PQR | 666          |
    |     Dick |  08-01-2023 |     XYZ | 222          |
    |      Tom |  05-01-2023 |     ABC | 111          |
    |    Harry |  05-01-2023 |     PQR | 666          |
    |    Harry |  06-01-2023 |     PQR | 666          |
    |    Harry |  07-01-2023 |     PQR | 666          |
     ---------- ------------- --------- --------------

Expected Result: I have a list of memberIds -> ABC, XYZ, PQR (This list can be any size approx 200 max) I need to select the latest 3 orders for each member.

     ---------- ------------- --------- -------------- 
    | Name     | orderDate   | memberId| OrderId      |
     ---------- ------------- --------- -------------- 
    |      Tom |  01-01-2023 |     ABC | 111          |
    |      Tom |  02-01-2023 |     ABC | 111          |
    |      Tom |  04-01-2023 |     ABC | 111          |
    |     Dick |  02-01-2023 |     XYZ | 111          |
    |     Dick |  01-01-2023 |     XYZ | 111          |
    |     Dick |  03-01-2023 |     XYZ | 111          |
    |    Harry |  01-01-2023 |     PQR | 111          |
    |    Harry |  03-01-2023 |     PQR | 111          |
    |    Harry |  04-01-2023 |     PQR | 111          |
     ---------- ------------- --------- --------------

How can I write an optimized select query for the above scenario?

Currently, I created a DB2 hibernate SQL query dynamically in for loop. The problem with this approach is that my query length grows too big for DB2 and returns an error as I have 200 memberId

-101 The statement is too long or too complex

(SELECT * FROM orders WHERE memberId='ABC' ORDERBY orderDate DESC FETCH FIRST 3 ROWS ONLY)
UNION ALL
(SELECT * FROM orders WHERE memberId='XYZ' ORDERBY orderDate DESC FETCH FIRST 3 ROWS ONLY)
UNION ALL
(SELECT * FROM orders WHERE memberId='PQR' ORDERBY orderDate DESC FETCH FIRST 3 ROWS ONLY)

Probable Solutions:

Divide memberIds list into smaller batches, call select multiple times, and aggregate results in my java code.
Any other way to fetch this result from SQL?

Please share your opinion from a query or java point of view. Thank you

CodePudding user response：

You may use GLOBAL TEMPORARY TABLE to store your long member list.

DECLARE GLOBAL TEMPORARY TABLE SESSION.MEMBERS (MEMBERID CHAR(3)) 
WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

INSERT INTO SESSION.MEMBERS VALUES 'ABC';
...
INSERT INTO SESSION.MEMBERS VALUES 'XYZ';

WITH MYTAB (Name, orderDate, memberId, OrderId) AS
(
VALUES
  ('  Tom', date ('01.01.2023'), 'ABC', 111)
, (' Dick', date ('01.01.2023'), 'XYZ', 222)
, ('Harry', date ('01.01.2023'), 'PQR', 666)
, (' Dick', date ('01.01.2023'), 'XYZ', 222)
, ('  Tom', date ('02.01.2023'), 'ABC', 111)
, ('Harry', date ('03.01.2023'), 'PQR', 666)
, (' Dick', date ('03.01.2023'), 'XYZ', 222)
, ('  Tom', date ('04.01.2023'), 'ABC', 111)
, (' Dick', date ('06.01.2023'), 'XYZ', 222)
, (' Dick', date ('07.01.2023'), 'XYZ', 222)
, ('Harry', date ('04.01.2023'), 'PQR', 666)
, (' Dick', date ('08.01.2023'), 'XYZ', 222)
, ('  Tom', date ('05.01.2023'), 'ABC', 111)
, ('Harry', date ('05.01.2023'), 'PQR', 666)
, ('Harry', date ('06.01.2023'), 'PQR', 666)
, ('Harry', date ('07.01.2023'), 'PQR', 666)
)
SELECT Name, orderDate, memberId, OrderID
FROM
(
SELECT 
    T.*
  , ROW_NUMBER () OVER (PARTITION BY Name ORDER BY orderDate DESC) AS RN_
FROM MYTAB T
WHERE EXISTS (SELECT 1 FROM SESSION.MEMBERS M WHERE M.MEMBERID = T.MEMBERID)
) T
WHERE RN_ <= 3
ORDER BY Name, orderDate DESC;

NAME	ORDERDATE	MEMBERID	ORDERID
Tom	2023-01-05	ABC	111
Tom	2023-01-04	ABC	111
Tom	2023-01-02	ABC	111
Dick	2023-01-08	XYZ	222
Dick	2023-01-07	XYZ	222
Dick	2023-01-06	XYZ	222

fiddle

CodePudding user response：

row_number() with partition by clause solves your use case.

Here is the query for the same

select rs.name, rs.order_date, rs.member_id, rs.order_id from (select *, row_number() over (partition by member_id order by order_date desc) as `order` from orders where member_id in ('XYZ', 'ABC')) as rs where rs.order <= 3;