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