The Next Value – How much higher is my salary than the next in (Dense) Rank – Oracle Analytical Functions

Lucas Jellema 1
0 0
Read Time:3 Minute, 16 Second

It is not terribly important. But since I found the solution, I thought I’d share it with you anyway. During our Oracle 7Up workshop on SQL last week we discussed at length the use of Analytical Functions. Part of that discussion is of course the use of the Rank function in combination with Partitions and the Lead function. 

One of the queries we specifically looked at was the one that tells us for each Employee how they rank within their own department in terms of their salary. And we threw in the difference for each employee’s salary and the next person on the list’s salary, for good measure.

....
That query looks something like this:

select ename
, deptno
, sal
, rank() over ( partition by deptno
order by sal desc
) sal_rank_in_dept
, sal - lead(sal, 1, null) over ( partition by deptno
order by sal desc
) lead_over_next_value
from emp
order
by deptno
, sal desc

The result of that query is:

 

Seems about right, does it not? Gives a lot of people a good feeling. They can see how they rank and what they earn more than the next person on the list. However, we have complaints from Mr. Scott. He says that this list is not correct, does not do him, justice. He is first on the list in Department 20, yet the list says he does not earn anything more than the next person on the list. Well, that is what it says. And it is the correct answer: SCOTT has the same salary as FORD. By coincidence, SCOTT is ranked higher than FORD. That means that the lead_over_next_value for SCOTT is calculated with regard to FORD’s salary. Since SCOTT and FORD have the same salary, the outcome for SCOTT is a zero result.

Well, to please SCOTT, we can try to change the query. Instead of calculating the difference with the next person on the list – as we do right now – we can try to calculate the difference with the next value on the list (the next value that differs from the current value). In SCOTT’s case that means we compare his salary to the 2975 that is the next differing value in Department 20.

To cut a long story short, the query that returns that result is this one:

 select ename
, deptno
, sal
, rank() over ( partition by deptno
order by sal desc
) sal_rank_in_dept
, first_value(sal) over ( partition by deptno
order by sal desc
range between 1 following
and unbounded following
) next_different_salary
, sal - first_value(sal) over ( partition by deptno
order by sal desc
range between 1 following
and unbounded following
) lead_over_next_value
from emp
order
by deptno
, sal desc

The query result:

 

Now SCOTT has what he wants: he leads the next person with a different salary by 25! Good for him. 

We make use of a small yet neat trick: we create a sliding window defined using the RANGE clause. We want to have the value of the salary of the first person (first_value(sal) in our department (partition by deptno) that has a lower salary (order by sal desc) than the current record. By having the range start at 1 following, we ensure that we will not get the salary from someone with the same salary as the current employee.

Note: at first I tried to use LEAD( sal,1) with the same OVER() clause. However, in my 10.1.2 database this did not work: I got error messages:                           

range between 1 following
                           *
ERROR at line 9:
ORA-00907: missing right parenthesis

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “The Next Value – How much higher is my salary than the next in (Dense) Rank – Oracle Analytical Functions

  1. Nice example of using analytical functions.

    I don’t know if it’s a problem with my browser, but in the query results the last column is not visible (I can see the results if i save the picture and then open it in windows viewer).

Comments are closed.

Next Post

JDBC Rowset

Do you still think the only way to retrieve data from the database is to use the java.sql.ResultSet? Try the new (hum) javax.sql.RowSet. One of the coolest features is to use it in a disconnected manner. Sounds familiar? Take a look at the .NET DataSet, the only 😉 way for […]
%d bloggers like this: