CODEWITHSUNDEEP
sqloracle-databasepostgresql
Martin Mucha
Martin Mucha

Reputation: 3091

postgres/oracle: get last non-null value within group

I have data which I have to 'group by'. In each resultant group, there are rows with multiple columns which has to be treated as such: for each such given column, return non-null, most current value. So I have to 'group by'(gb) whole table, and find 'max-like(NUM)' for every column(below represented as NUM). max-like function sorts by temporal column, below represented as 'time'. In another words, group by 'gb', sort group by 'time' desc nulls last, get first item in group.

Sorry, for that convoluted description. I hope it's clear. Any idea how to write that sql query (oracle/postgres)?

example

CREATE TABLE test (
  gb integer,
  NUM INTEGER,
  time integer
);

--rows=groups, columns=time; so in first row=group data 
--sorted by time are from left to right the middle value 
--in triplet, thus, 2,1,3. Ie. most current non-null value in time is 3.
insert into test VALUES (1,2,1),(1,1,2),(1,3,3);--3
insert into test VALUES (2,1,1),(2,2,2),(2,3,3);--3
insert into test VALUES (3,3,1),(3,2,2),(3,1,3);--1
insert into test VALUES (4,3,1),(4,2,2),(4,null,3);--2
insert into test VALUES (5,2,1),(5,3,2),(5,null,3);--3
insert into test VALUES (6,2,1),(6,null,2),(6,null,3);--2

query

select
  t.gb,
  '<magic goes here>'
from test t
GROUP BY t.gb ORDER BY t.gb;

is expected to return 

1 | 3
2 | 3
3 | 1
4 | 2
5 | 3
6 | 2

Upvotes: 0

Views: 1690

Answers (4)

wolfrevokcats
wolfrevokcats

Reputation: 2100

In Oracle the simplest way is:

SELECT gb, max(num) keep (DENSE_RANK LAST ORDER BY nvl2(num,time,NULL) NULLS first ) r 
FROM test 
GROUP BY gb

SQLfiddle

There is also a "group-less" approach:

SELECT DISTINCT gb, last_value(num ignore nulls)over(PARTITION BY gb ORDER BY time 
       RANGE BETWEEN UNBOUNDED preceding AND UNBOUNDED following) num
FROM test ORDER BY gb

SQLfiddle

GB  NUM 
--- ----
1   3   
2   3   
3   1   
4   2   
5   3   
6   2

Upvotes: 5

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Here is a solution in standard SQL:

with grp as
(
  select distinct gb from mytable
)
SELECT 
  gb,
  (
    select num 
    from mytable m 
    where m.gb = grp.gb and m.num is not null 
    order by thetime desc 
    fetch first row only
  ) as n
FROM grp
order by grp.gb\\

You need one such subquery per column.

In Oracle this query only works as of version 12c; former versions don't feature the FETCH FIRST n ROWS clause.

Here is an alternative, also standard SQL, which does work in older Oracle versions:

with grp as
(
  select distinct gb from mytable
)
SELECT 
  gb,
  (
    select num 
    from mytable m 
    where m.gb = grp.gb and thetime = 
    (
      select max(thetime) 
      from mytable m2 
      where m2.gb = m.gb and m2.num is not null
    )
  ) as n
FROM grp
order by grp.gb\\

Rextester demo: http://rextester.com/QUO91858

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246578

This is probably the simplest solution that works in both Oracle and PostgreSQL:

SELECT DISTINCT
   gb,
   first_value(num) OVER (PARTITION BY gb ORDER BY time DESC)
FROM test
WHERE num IS NOT NULL
ORDER BY gb;

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You could use row_number to assign an increasing number for each row with the same gb. Order those rows by time, and only display the first one:

select  gb
,       num
from    (
        select  row_number() over (partition by gb order by time desc) rn
        ,       *
        from    test
        where   num is not null
        ) sub
where   rn = 1  -- Row with highest time per (gb) group

Working example at SQL Fiddle.

Upvotes: 3

Related Questions