Go Premium for a chance to win a PS4. Enter to Win

x
Solved

Previous Current Next Record

Posted on 2009-04-08
Medium Priority
853 Views
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.
0
Question by:k_murli_krishna
• 4
• 2
• 2

LVL 22

Expert Comment

ID: 24097502
Can you expand upon what you mean in Point Number 5 (copied below) and what you are really trying to do?  (I know you have stated that you are tring to find out whether a record is followed by one with a differnt DeptNo but my question is, what is the purpose of doing this?)
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.

Can you use a stored procedure in achieving this ?
Can you use a temporary table in achieving this ?
0

LVL 12

Expert Comment

ID: 24100031
Understanding that I don't have DB2 to run against, I believe the query in the code snippet below should work.  (Solution relies on subselects, so the database needs to support that; hopefully your version of DB2 does.)  I did try this solution (with appropriate replacements) against my PostgreSQL database and it worked as I expected.

As you can see, there are three queries that are then UNION'ed together.  All assume that "_value_" is replaced with the specific 'empno' as you described above.  The middle query is simply returning the values for that specified 'empno'.

For getting the previous (potential) empno within a department, we are really looking for the maximum 'empno' which is less than the specified one, limiting to the same 'deptno' as the specified 'empno' _and_ having a 'sal' greater than or equal to 5000.  Once we have that 'empno' we just request that record and get all the desired columns.

Similarly, for the next (potential) empno within a department, we are really looking for the minimum 'empno' which is greater than the specified one, with the same limits as above.  Again, once we have that 'empno' we just request that record and get the desired columns.

With all three retrieved, we UNION so that they are one table of results.

Note that this solution should also automatically handle the case where the specified 'empno' is the lowest or highest within the department; the first subselect will not find any minimum or maximum value, because there will be no records that exceed the value.  So the query would be like "empno in ()", which means no record is returned for that one, but the other two queries do return something and thus you get the 2 desired records.

(Of course, if the database does not support subselects, then one just has to break the subselects into individually run queries, saving the results to then manually input into the next query.)
``````SELECT deptno, empno, ename FROM emp
WHERE empno IN
(SELECT max(empno) FROM emp
WHERE empno < _value_
AND deptno IN (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000)
UNION
SELECT deptno, empno, ename FROM emp
WHERE empno = _value_
UNION
SELECT deptno, empno, ename FROM emp
WHERE empno IN
(SELECT MIN(empno) FROM emp
WHERE empno > _value_
AND deptno IN (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000)
``````
0

LVL 17

Author Comment

ID: 24100092
ORDER BY deptno ASC, empno ASC:
deptno     empno
--------     --------
10             40
10             60
20             10
20             20
20             30
30             50
30            70
30            80

Suppose, we feed empno = 10, then deptno = 20 in current record, = 10 in previous record, = 20 in next record. So, to determine whether in previous and next deptno is same/different is first task. Then within same deptno, empno is sorted in ASC order but acroos different deptno, sorting order of empno as you can see is not guaranteed.

No, we cannot use stored procedure but it could be achieved partly in one/more queries & partly in Java. Temporary tables can be used only inside stored procedure. This is a project requirement and I just gave its simple situation equivalent.
0

LVL 12

Expert Comment

ID: 24100143
Based on what I read in your original problem explanation, I assumed that you only wanted the previous and next records within the same department of the specified 'empno'.  If this is _not_ correct (that is, you want the previous and next record regardless of department, from the original ordering), then it becomes more difficult.  I'll wait for your clarification on this.
0

LVL 22

Expert Comment

ID: 24100355
k_murli_krishna,
No, we cannot use stored procedure but it could be achieved partly in one/more queries & partly in Java.  Temporary tables can be used only inside stored procedure.
Based upon the above statements, it looks like you are probably going to have to pull the data using the SQL statement and then process it using Java.
Not having stored procedures available to you and, especially, not having temporary tables available, this becomes extremely tricky.  The sitcky spot is that you appear to want the preceding and succeeding DeptNo's checked regardless of the empno.
I assume that this is associated with a need to display the different departments on different pages or to put some sort of header between them but, without really knowing what you are trying to accomplish in the end, I cannot offer an alternative to the use of Java to process the data.
0

LVL 12

Accepted Solution

cminear earned 2000 total points
ID: 24100589
OK, I didn't wait for an answer.  If you do want the previous or next entry regardless of department, then I believe this solution should work.  It relies on the use of COALESCE; a quick Google search suggests that DB2 supports it, but I have no idea whether your version does.

This one works off of the prior solution.  Now, instead of letting the NULL empno proceed to return an empty record, we use this as the key to then proceed to look for the adjacent 'deptno' (previous or next) and then look for the extreme empno within that department: max empno for the previous department; min empno for the next department.

And just like the prior solution, if you specify the first or last empno of the results, then that will still return NULL and thus no record is returned.
``````SELECT deptno, empno, ename FROM emp
WHERE empno IN
(COALESCE((SELECT max(empno) FROM emp
WHERE empno < _value_
AND deptno IN (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000),
(SELECT max(empno) FROM emp
WHERE deptno IN (SELECT max(deptno) FROM emp
WHERE deptno < (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000))))
UNION
SELECT deptno, empno, ename FROM emp
WHERE empno = _value_
UNION
SELECT deptno, empno, ename FROM emp
WHERE empno IN
(COALESCE((SELECT MIN(empno) FROM emp
WHERE empno > _value_
AND deptno IN (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000),
(SELECT min(empno) FROM emp
WHERE deptno IN (SELECT min(deptno) FROM emp
WHERE deptno > (SELECT deptno FROM emp
WHERE empno = _value_)
AND sal >= 5000))))
``````
0

LVL 12

Expert Comment

ID: 24100810
Just as an illustration of the two different solutions, I took your sample data, and added names and values for sal (all 6000), and put them into an SQLite database.  Here is the table:
deptno|empno|ename|sal
10|40|alice|6000
10|60|bob|6000
20|10|carol|6000
20|20|david|6000
20|30|edward|6000
30|50|fred|6000
30|70|grace|6000
30|80|hugh|6000

When I run the first solution I gave against this set, with "10" in for _value_, here are the results I get:
20|10|carol
20|20|david

When I run the second solution I gave against this set, again with "10" in for _value_, here are the results I get:
10|60|bob
20|10|carol
20|20|david

So if neither of these is what you want, then please let me know what I'm missing.
0

LVL 17

Author Closing Comment

ID: 31568037
Thank you very much.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month5 days, 22 hours left to enroll