Previous Current Next Record
Posted on 2009-04-08
1) I have emp table with empno(pk), ename, sal and deptno(fk) columns
2) I have following query:
SELECT deptno, empno, ename FROM emp
WHERE sal >= 5000
ORDER BY deptno ASC, empno ASC;
3) Now I will feed an existing value to empno and I want in above result data based on ORDER BY deptno, empno record above this and below this if it is a record in the middle. If it is first record, then that and next record. If it is last record, then that and previous record.
4) ROW_NUMBER is not supported in our DB2 database iseries V5R3. Hence I need a generic solution which will work on all RDBMS.
5) It all hinges around the need to find whether next/previous to current deptno is same one or different one since for same deptno empno will be sorted in ASC order but across deptno's sorting order is not at all guaranteed.
6) If it was only ORDER BY empno ASC, using common table expression with WITH clause, empno = ? OR empno = (SELECT MIN(empno) FROM cte WHERE empno > ?) OR empno = (SELECT MAX(empno) FROM cte WHERE empno < ?) will do the trick but due to ORDER BY deptno, empno, the issue has become complex.
7) You can give solution in one query or more i.e. step wise.