Solved

Previous Current Next Record

Posted on 2009-04-08
8
828 Views
Last Modified: 2012-05-06
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
Comment
Question by:k_murli_krishna
  • 4
  • 2
  • 2
8 Comments
 
LVL 22

Expert Comment

by:8080_Diver
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

by:cminear
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)

Open in new window

0
 
LVL 17

Author Comment

by:k_murli_krishna
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

by:cminear
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:8080_Diver
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

by:
cminear earned 500 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))))

Open in new window

0
 
LVL 12

Expert Comment

by:cminear
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

by:k_murli_krishna
ID: 31568037
Thank you very much.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now