DB2, MySQL, PostgreSQL, SQL Server and Oracle

Although it is possible to develop a solution using a self-join, the window function LEAD OVER is perfect for this type of problem, and more intuitive. The function LEAD OVER allows you to examine other rows without performing a self join (though the function must impose order on the result set to do so). Consider the results of the inline view (lines 3–5) for IDs 1 and 4:

	select *
	  from (
	select proj_id, proj_start, proj_end,
	       lead(proj_start)over(order by proj_id) next_proj_start
	  from v
	       )
	 where proj_id in ( 1, 4 )

	PROJ_ID PROJ_START  PROJ_END    NEXT_PROJ_START
	------- ----------- ----------- ---------------
	      1 01-JAN-2020 02-JAN-2020 02-JAN-2020
	      4 04-JAN-2020 05-JAN-2020 06-JAN-2020

Examining the above snippet of code and its result set, it is particularly easy to see why PROJ_ID 4 is excluded from the final result set of the complete solution. It’s excluded because its PROJ_END date of 05-JAN-2005 does not match the “next” project’s start date of 06-JAN-2005.

The function LEAD OVER is extremely handy when it comes to problems such as this one, particularly when examining partial results. When working with window functions, keep in mind that they are evaluated after the FROM and WHERE clauses, so the LEAD OVER function in the preceding query must be embedded within an inline view. Otherwise the LEAD OVER function is applied to the result set after the WHERE clause has filtered out all rows except for PROJ_ID’s 1 and 4.

Now, depending on how you view the data, you may very well want to include PROJ_ID 4 in the final result set. Consider the first five rows from view V:

	select *
	  from V
	 where proj_id <= 5

	PROJ_ID PROJ_START  PROJ_END
	------- ----------- -----------
	      1 01-JAN-2020 02-JAN-2020
	      2 02-JAN-2020 03-JAN-2020
	      3 03-JAN-2020 04-JAN-2020
	      4 04-JAN-2020 05-JAN-2020
	      5 06-JAN-2020 07-JAN-2020

If your requirement is such that PROJ_ID 4 is in fact contiguous (because PROJ_ START for PROJ_ID 4 matches PROJ_END for PROJ_ID 3), and that only PROJ_ ID 5 should be discarded, the proposed solution for this recipe is incorrect (!), or at the very least, incomplete:

	select proj_id, proj_start, proj_end
	  from (
	select proj_id, proj_start, proj_end, 
	       lead(proj_start)over(order by proj_id) next_start
	  from V
	where proj_id <= 5
	      )
	where proj_end = next_start

	PROJ_ID PROJ_START  PROJ_END
	------- ----------- -----------
	      1 01-JAN-2020 02-JAN-2020
	      2 02-JAN-2020 03-JAN-2020
	      3 03-JAN-2020 04-JAN-2020

If you believe PROJ_ID 4 should be included, simply add LAG OVER to the query and use an additional filter in the WHERE clause:

	select proj_id, proj_start, proj_end
	  from (
	select proj_id, proj_start, proj_end, 
	       lead(proj_start)over(order by proj_id) next_start,
	       lag(proj_end)over(order by proj_id) last_end
	  from V
	where proj_id <= 5
	      )
	where proj_end = next_start
	   or proj_start = last_end

	PROJ_ID PROJ_START  PROJ_END
	------- ----------- -----------
	      1 01-JAN-2005 02-JAN-2005
	      2 02-JAN-2005 03-JAN-2005
	      3 03-JAN-2005 04-JAN-2005
	      4 04-JAN-2005 05-JAN-2005

Now PROJ_ID 4 is included in the final result set, and only the evil PROJ_ID 5 is excluded. Please consider your exact requirements when applying these recipes to your code.

Discussion

The first step is to use the LEAD OVER window function to find the “next” salary for each employee within her department. The employees hired last in each department will have a NULL value for NEXT_SAL:

	select deptno,ename,sal,hiredate,
	       lead(sal)over(partition by deptno order by hiredate) as next_sal
	  from emp

	DEPTNO ENAME             SAL HIREDATE      NEXT_SAL
	------ ---------- ---------- ----------- ----------
	    10 CLARK            2450 09-JUN-1981       5000
	    10 KING             5000 17-NOV-1981       1300
	    10 MILLER           1300 23-JAN-1982
	    20 SMITH             800 17-DEC-1980       2975
	    20 JONES            2975 02-APR-1981       3000
	    20 FORD             3000 03-DEC-1981       3000
	    20 SCOTT            3000 09-DEC-1982       1100
	    20 ADAMS            1100 12-JAN-1983
	    30 ALLEN            1600 20-FEB-1981       1250
	    30 WARD             1250 22-FEB-1981       2850
	    30 BLAKE            2850 01-MAY-1981       1500
	    30 TURNER           1500 08-SEP-1981       1250
	    30 MARTIN           1250 28-SEP-1981        950
	    30 JAMES             950 03-DEC-1981

The next step is to take the difference between each employee’s salary and the salary of the employee hired immediately after her in the same department:

	select deptno,ename,sal,hiredate, sal-next_sal diff
	  from (
	select deptno,ename,sal,hiredate,
	       lead(sal)over(partition by deptno order by hiredate) next_sal
	  from emp
	       )

	DEPTNO ENAME             SAL HIREDATE          DIFF
	------ ---------- ---------- ----------- ----------
	    10 CLARK            2450 09-JUN-1981      -2550
	    10 KING             5000 17-NOV-1981       3700
	    10 MILLER           1300 23-JAN-1982
	    20 SMITH             800 17-DEC-1980      -2175
	    20 JONES            2975 02-APR-1981        -25
	    20 FORD             3000 03-DEC-1981          0
	    20 SCOTT            3000 09-DEC-1982       1900
	    20 ADAMS            1100 12-JAN-1983
	    30 ALLEN            1600 20-FEB-1981        350
	    30 WARD             1250 22-FEB-1981      -1600
	    30 BLAKE            2850 01-MAY-1981       1350
	    30 TURNER           1500 08-SEP-1981        250
	    30 MARTIN           1250 28-SEP-1981        300
	    30 JAMES             950 03-DEC-1981

The next step is to use the coalesce function to insert “N/A” when there is no next salary. To be able to return “N/A” you must cast the value of DIFF to a string:

	select deptno,ename,sal,hiredate,
	       nvl(to_char(sal-next_sal),'N/A') diff
	  from (
	select deptno,ename,sal,hiredate,
	       lead(sal)over(partition by deptno order by hiredate) next_sal
	  from emp
	       )

	DEPTNO ENAME             SAL HIREDATE    DIFF
	------ ---------- ---------- ----------- ---------------
	    10 CLARK            2450 09-JUN-1981 -2550
	    10 KING             5000 17-NOV-1981 3700
	    10 MILLER           1300 23-JAN-1982 N/A
	    20 SMITH             800 17-DEC-1980 -2175
	    20 JONES            2975 02-APR-1981 -25
	    20 FORD             3000 03-DEC-1981 0
	    20 SCOTT            3000 09-DEC-1982 1900
	    20 ADAMS            1100 12-JAN-1983 N/A
	    30 ALLEN            1600 20-FEB-1981 350
	    30 WARD             1250 22-FEB-1981 -1600
	    30 BLAKE            2850 01-MAY-1981 1350
	    30 TURNER           1500 08-SEP-1981 250
	    30 MARTIN           1250 28-SEP-1981 300
	    30 JAMES             950 03-DEC-1981 N/A

While the majority of the solutions provided in this book do not deal with “what if” scenarios (for the sake of readability and the author’s sanity), the scenario involving duplicates when using the LEAD OVER function in this manner must be discussed. In the simple sample data in table EMP, no employees have duplicate HIREDATEs, yet this is a very likely situation. Normally, I would not discuss a “what if” situation such as duplicates (since there aren’t any in table EMP), but the workaround involving LEAD may not be immediately obvious. Consider the following query, which returns the difference in SAL between the employees in DEPTNO 10 (the difference is performed in the order in which they were hired):

	select deptno,ename,sal,hiredate,
	       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
	  from (
	select deptno,ename,sal,hiredate,
	       lead(sal)over(partition by deptno
	                         order by hiredate) next_sal
	  from emp
	 where deptno=10 and empno > 10
	       )

	DEPTNO ENAME    SAL HIREDATE    DIFF
	------ ------ ----- ----------- ----------
	    10 CLARK   2450 09-JUN-1981      -2550
	    10 KING    5000 17-NOV-1981       3700
	    10 MILLER  1300 23-JAN-1982        N/A

This solution is correct considering the data in table EMP but, if there were duplicate rows, the solution would fail. Consider the example below, showing four more employees hired on the same day as KING:

	insert into emp (empno,ename,deptno,sal,hiredate)
	values (1,'ant',10,1000,to_date('17-NOV-1981'))

	insert into emp (empno,ename,deptno,sal,hiredate)
	values (2,'joe',10,1500,to_date('17-NOV-1981'))

	insert into emp (empno,ename,deptno,sal,hiredate)
	values (3,'jim',10,1600,to_date('17-NOV-1981'))

	insert into emp (empno,ename,deptno,sal,hiredate)
	values (4,'jon',10,1700,to_date('17-NOV-1981'))

	select deptno,ename,sal,hiredate,
	       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
	  from (
	select deptno,ename,sal,hiredate,
	       lead(sal)over(partition by deptno
	                         order by hiredate) next_sal
	  from emp
	 where deptno=10
	       )

	DEPTNO ENAME    SAL HIREDATE    DIFF
	------ ------ ----- ----------- ----------
	    10 CLARK   2450 09-JUN-1981       1450
	    10 ant     1000 17-NOV-1981       -500
	    10 joe     1500 17-NOV-1981      -3500
	    10 KING    5000 17-NOV-1981       3400
	    10 jim     1600 17-NOV-1981       -100
	    10 jon     1700 17-NOV-1981        400
	    10 MILLER  1300 23-JAN-1982        N/A

You’ll notice that with the exception of employee JON, all employees hired on the same date (November 17) evaluate their salary against another employee hired on the same date! This is incorrect. All employees hired on November 17 should have the difference of salary computed against MILLER’s salary, not another employee hired on November 17. Take, for example, employee ANT. The value for DIFF for ANT is–500 because ANT’s SAL is compared with JOE’s SAL and is 500 less than JOE’s SAL, hence the value of–500. The correct value for DIFF for employee ANT should be–300 because ANT makes 300 less than MILLER, who is the next employee hired by HIREDATE. The reason the solution seems to not work is due to the default behavior of Oracle’s LEAD OVER function. By default, LEAD OVER only looks ahead one row. So, for employee ANT, the next SAL based on HIREDATE is JOE’s SAL, because LEAD OVER simply looks one row ahead and doesn’t skip duplicates. Fortunately, Oracle planned for such a situation and allows you to pass an additional parameter to LEAD OVER to determine how far ahead it should look. In the example above, the solution is simply a matter of counting: find the distance from each employee hired on November 17 to January 23 (MILLER’s HIREDATE). The solution below shows how to accomplish this:

	select deptno,ename,sal,hiredate,
	       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
	  from (
	select deptno,ename,sal,hiredate,
	       lead(sal,cnt-rn+1)over(partition by deptno
	                         order by hiredate) next_sal
	  from (
	select deptno,ename,sal,hiredate,
	       count(*)over(partition by deptno,hiredate) cnt,
	       row_number()over(partition by deptno,hiredate order by sal) rn
	  from emp
	 where deptno=10
	       )
	       )

	DEPTNO ENAME     SAL HIREDATE    DIFF
	------ ------  ----- ----------- ----------
	    10 CLARK    2450 09-JUN-1981       1450
	    10 ant      1000 17-NOV-1981       -300
	    10 joe      1500 17-NOV-1981        200
	    10 jim      1600 17-NOV-1981        300
	    10 jon      1700 17-NOV-1981        400
	    10 KING     5000 17-NOV-1981       3700
	    10 MILLER   1300 23-JAN-1982        N/A

Now the solution is correct. As you can see, all the employees hired on November 17 now have their salaries compared with MILLER’s salary. Inspecting the results, employee ANT now has a value of–300 for DIFF, which is what we were hoping for. If it isn’t immediately obvious, the expression passed to LEAD OVER; CNT-RN+1 is simply the distance from each employee hired on November 17 to MILLER. Consider the inline view below, which shows the values for CNT and RN:

	select deptno,ename,sal,hiredate,
	       count(*)over(partition by deptno,hiredate) cnt,
	       row_number()over(partition by deptno,hiredate order by sal) rn
	  from emp
	 where deptno=10

	DEPTNO ENAME    SAL HIREDATE           CNT         RN
	------ ------ ----- ----------- ---------- ----------
	    10 CLARK   2450 09-JUN-1981          1          1
	    10 ant     1000 17-NOV-1981          5          1
	    10 joe     1500 17-NOV-1981          5          2
	    10 jim     1600 17-NOV-1981          5          3
	    10 jon     1700 17-NOV-1981          5          4
	    10 KING    5000 17-NOV-1981          5          5
	    10 MILLER  1300 23-JAN-1982          1          1

The value for CNT represents, for each employee with a duplicate HIREDATE, how many duplicates there are in total for their HIREDATE. The value for RN represents a ranking for the employees in DEPTNO 10. The rank is partitioned by DEPTNO and HIREDATE so only employees with a HIREDATE that another employee has will have a value greater than one. The ranking is sorted by SAL (this is arbitrary; SAL is convenient, but we could have just as easily chosen EMPNO). Now that you know how many total duplicates there are and you have a ranking of each duplicate, the distance to MILLER is simply the total number of duplicates minus the current rank plus one (CNT-RN+1). The results of the distance calculation and its effect on LEAD OVER are shown below:

	select deptno,ename,sal,hiredate,
	       lead(sal)over(partition by deptno
	                         order by hiredate) incorrect,
	       cnt-rn+1 distance,
	       lead(sal,cnt-rn+1)over(partition by deptno
	                         order by hiredate) correct
	  from (
	select deptno,ename,sal,hiredate,
	       count(*)over(partition by deptno,hiredate) cnt,
	       row_number()over(partition by deptno,hiredate
	                            order by sal) rn
	  from emp
	 where deptno=10
	       )

	DEPTNO ENAME    SAL HIREDATE    INCORRECT    DISTANCE    CORRECT
	------ ------ ----- ----------- ---------- ---------- ----------
	    10 CLARK   2450 09-JUN-1981       1000          1       1000
	    10 ant     1000 17-NOV-1981       1500          5       1300
	    10 joe     1500 17-NOV-1981       1600          4       1300
	    10 jim     1600 17-NOV-1981       1700          3       1300
	    10 jon     1700 17-NOV-1981       5000          2       1300
	    10 KING    5000 17-NOV-1981       1300          1       1300
	    10 MILLER  1300 23-JAN-1982                     1

Now you can clearly see the effect that you have when you pass the correct distance to LEAD OVER. The rows for INCORRECT represent the values returned by LEAD OVER using a default distance of one. The rows for CORRECT represent the values returned by LEAD OVER using the proper distance for each employee with a duplicate HIREDATE to MILLER. At this point, all that is left is to find the difference between CORRECT and SAL for each row, which has already been shown.

DB2

Use table EMP as a pivot table (because it has 14 rows) and the built-in function YEAR to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:

	 1 select x.yr, coalesce(y.cnt,0) cnt
	 2   from (
	 3 select year(min(hiredate)over()) -
	 4        mod(year(min(hiredate)over()),10) +
	 5        row_number()over()-1 yr
	 6   from emp fetch first 10 rows only
	 7        ) x
	 8   left join
	 9        (
	10 select year(hiredate) yr1, count(*) cnt
	11   from emp
	12  group by year(hiredate)
	13        ) y
	14     on ( x.yr = y.yr1 )

Oracle

	 1 select x.yr, coalesce(cnt,0) cnt
	 2   from (
	 3 select extract(year from min(hiredate)over()) -
	 4        mod(extract(year from min(hiredate)over()),10) +
	 5        rownum-1 yr
	 6   from emp
	 7  where rownum <= 10
	 8        ) x
	 9   left join
	10        (
	11 select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
	12   from emp
	13  group by to_number(to_char(hiredate,'YYYY'))
	14        ) y
	15     on ( x.yr = y.yr )

PostgreSQL and MySQL

Use table T10 as a pivot table (because it has 10 rows) and the built-in function EXTRACT to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:

	 1 select y.yr, coalesce(x.cnt,0) as cnt
	 2   from (
	 3 selectmin_year-mod(cast(min_year as int),10)+rn as yr
	 4   from (
	 5 select (select min(extract(year from hiredate))
	 6           from emp) as min_year,
	 7        id-1 as rn
	 8   from t10
	 9        ) a
	10        ) y
	11   left join
	12        (
	13 select extract(year from hiredate) as yr, count(*) as cnt
	14   from emp
	15  group by extract(year from hiredate)
	16        ) x
	17     on ( y.yr = x.yr )

SQL Server

Use table EMP as a pivot table (because it has 14 rows) and the built-in function YEAR to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:

	 1 select x.yr, coalesce(y.cnt,0) cnt
	 2   from (
	 3 select top (10)
	 4        (year(min(hiredate)over()) -
	 5         year(min(hiredate)over())%10)+
	 6         row_number()over(order by hiredate)-1 yr
	 7   from emp
	 8        ) x
	 9   left join
	10        (
	11 select year(hiredate) yr, count(*) cnt
	12   from emp
	13  group by year(hiredate)
	14        ) y
	15     on ( x.yr = y.yr )

DB2 and SQL Server

Use the recursive WITH clause to generate a sequence of rows with incrementing values. Using a recursive CTE will in fact work with the majority of RDBMS’s today.

	 1 with x (id)
	 2 as (
	 3 select 1
	 4  union all
	 5 select id+1
	 6   from x
	 7  where id+1 <= 10
	 8 )
	 9 select * from x

Oracle

In Oracle Database you can generate rows using the MODEL clause:

	1 select array id
	2   from dual
	3  model
	4    dimension by (0 idx)
	5    measures(1 array)
	6    rules iterate (10) (
	7      array[iteration_number] = iteration_number+1
	8    )

PostgreSQL

Use the very handy function GENERATE_SERIES, which is designed for the express purpose of generating rows:

	1 select id
	2   from generate_series (1, 10) x(id)

DB2 and SQL Server

The recursive WITH clause increments ID (which starts at 1) until the WHERE clause is satisfied. To kick things off you must generate one row having the value 1. You can do this by selecting 1 from a one-row table or, in the case of DB2, by using the VALUES clause to create a one-row result set.

Oracle

In the MODEL clause solution, there is an explicit ITERATE command that allows you to generate multiple rows. Without the ITERATE clause, only one row will be returned, since DUAL has only one row. For example:

	select array id
	  from dual
	model
	  dimension by (0 idx)
	  measures(1 array)
	  rules ()

	 ID
	 --
	  1

The MODEL clause not only allows you array access to rows, it allows you to easily “create” or return rows that are not in the table you are selecting against. In this solution, IDX is the array index (location of a specific value in the array) and ARRAY (aliased ID) is the “array” of rows. The first row defaults to 1 and can be referenced with ARRAY[0]. Oracle provides the function ITERATION_NUMBER so you can track the number of times you’ve iterated. The solution iterates 10 times, causing ITERATION_NUMBER to go from 0 to 9. Adding 1 to each of those values yields the results 1 through 10.

It may be easier to visualize what’s happening with the model clause if you execute the following query:

	select 'array['||idx||'] = '||array as output
	  from dual
	 model
	   dimension by (0 idx)
	   measures(1 array)
	   rules iterate (10) (
	     array[iteration_number] = iteration_number+1
	   )

	OUTPUT
	------------------
	array[0] = 1
	array[1] = 2
	array[2] = 3
	array[3] = 4
	array[4] = 5
	array[5] = 6
	array[6] = 7
	array[7] = 8
	array[8] = 9
	array[9] = 10

PostgreSQL

All the work is done by the function GENERATE_SERIES. The function accepts three parameters, all numeric values. The first parameter is the start value, the second parameter is the ending value, and the third parameter is an optional “step” value (how much each value is incremented by). If you do not pass a third parameter, the increment defaults to 1.

The GENERATE_SERIES function is flexible enough so that you do not have to hardcode parameters. For example, if you wanted to return five rows starting from value 10 and ending with value 30, incrementing by 5 such that the result set is the following:

	 ID
	---
	 10
	 15
	 20
	 25
	 30

you can be creative and do something like this:

	select id
	  from generate_series(
	         (select min(deptno) from emp),
	         (select max(deptno) from emp),
	         5
	       ) x(id)

Notice here that the actual values passed to GENERATE_SERIES are not known when the query is written. Instead, they are generated by subqueries when the main query executes.