P3M P3M - 2 months ago 7
SQL Question

SQL query for segregating max and min values of a column in two different columns say Val1 and Val2

I have below table:


ID  DateVal  Val

1  1/1/2010  a

1  2/2/2010  b

1  3/3/2010  c

2  4/4/2010  d

2  5/5/2010  e

2  6/6/2010  f

3  7/7/2010  g

3  8/8/2010  h

3  9/9/2010  i


I need below:


ID  Val1  Val2

1      a     c

2      d     f

3      g     i




i.e. the Val at min date in column 'Val1' and Val and max date in column 'Val2'.

What all queries are there to achieve this output and which one is easiest?

Answer

DDL

with T as (
select 1 as id, to_date('01.01.2010','DD.MM.YYYY') dt, 'a' val
from dual union all
select 1 as id, to_date('02.02.2010','DD.MM.YYYY') dt, 'b' val
from dual union all
select 1 as id, to_date('03.03.2010','DD.MM.YYYY') dt, 'c' val
from dual union all
select 2 as id, to_date('04.04.2010','DD.MM.YYYY') dt, 'd' val
from dual union all
select 2 as id, to_date('05.05.2010','DD.MM.YYYY') dt, 'e' val
from dual union all
select 2 as id, to_date('06.06.2010','DD.MM.YYYY') dt, 'f' val
from dual union all
select 3 as id, to_date('07.07.2010','DD.MM.YYYY') dt, 'g' val
from dual union all
select 3 as id, to_date('08.08.2010','DD.MM.YYYY') dt, 'h' val
from dual union all
select 3 as id, to_date('09.09.2010','DD.MM.YYYY') dt, 'i' val
from dual)

Code

  select 
  id,
  max(val) keep (dense_rank first order by dt) as maxs,
  max(val) keep (dense_rank first order by dt desc) as mins from t 
group by id