Equivalent of Oracle KEEP can be in DB2 using a nested query OVER (PARTITION BY ..) and then using MAX and MIN on top of it without having any extra sort.
It is best explained by an example:
Case-1: Oracle query using KEEP function:
CREATE TABLE emp(id INTEGER, name VARCHAR(20), salary INT, dept INT)
/
INSERT INTO emp VALUES (100, 'Jones', 30000, 5)
/
INSERT INTO emp VALUES (101, 'Smith', 25000, 3);
/
INSERT INTO emp VALUES (102, 'Shoemaker', 28000, 5)
/
INSERT INTO emp VALUES (103, 'Edwards', 29000, 3)
/
INSERT INTO emp VALUES (104, 'Lawrence', 27000, 3)
SELECT dept,
min(salary) as salary,
max(id) KEEP(DENSE_RANK FIRST ORDER BY salary) as id,
max(name) KEEP(DENSE_RANK FIRST ORDER BY salary) as name
FROM emp
GROUP BY dept
/
DEPT SALARY ID NAME
---------- ---------- ---------- --------------------
3 25000 101 Smith
5 28000 102 Shoemaker
Case-2: DB2 query using equivalent of KEEP function:
CREATE TABLE emp(id INTEGER, name VARCHAR(20), salary INT, dept INT);
INSERT INTO emp VALUES
(100, 'Jones', 30000, 5),
(101, 'Smith', 25000, 3),
(102, 'Shoemaker', 28000, 5),
(103, 'Edwards', 29000, 3),
(104, 'Lawrence', 27000, 3);
SELECT dept, MAX(salary) as salary, MAX(id) as id, max(name) as name
FROM (SELECT dept,
MIN(salary) OVER(PARTITION BY dept) as salary,
FIRST_VALUE(id) OVER(PARTITION BY dept ORDER BY salary) as id,
FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary) as name
FROM emp)
GROUP BY dept;
DEPT SALARY ID NAME
----------- ----------- ----------- --------------------
3 25000 101 Smith
5 28000 102 Shoemaker
2 record(s) selected.
Note: Thanks to Serge for providing an answer