CODEWITHSUNDEEP
sqldatabaseoracle11g
Shan
Shan

Reputation: 57

SQL for creating a range

I have the below table with 2 columns

Dept   |   Term  

A      |    2010
A      |    2011
B      |    2012
C      |    2013

I want to create a maximum and minimum value columns such that the output should look as the table below. The minimum value can be obtained using the min() function and grouping by term column. The maximum value should be the minimum value from the next group. 

Dept   |   min_Term  | max_term

A      |    2010     |  2012
B      |    2012     |  2013
C      |    2013     |  Null

Thanks for your help in advance!

Upvotes: 2

Views: 26

Answers (2)

Sentinel
Sentinel

Reputation: 6449

You can combine aggregate and analytic functions in one shot like so:

select dept
     , min(term) min_term
     , lead(min(term)) over (order by dept) max_term
  from table
 group by dept;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use the lead function to get the mininmum term value from the next row.

with t1 as (
select dept, min(term) mnterm
from table
group by dept)
, t2 as (select dept, mnterm as min_term, lead(mnterm) over(order by dept) as max_term
         from t1)
select * from t2;

Upvotes: 3

Related Questions