Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Previous Current Next Record

Posted on 2009-04-08
8
Medium Priority
?
845 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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
 
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 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))))

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 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