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

Question

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

No, this is not clear. The verbal description is confusing, and the sample returns only one column, so I get no clue what <magic goes here> should produce. — Laurenz Albe
– Laurenz Albe, Commented Mar 23, 2018 at 14:31
@LaurenzAlbe: The question says "group by 'gb', sort group by 'time' desc nulls last, get first item in group." That's enough information to answer. There's even example output! — Andomar
– Andomar, Commented Mar 23, 2018 at 14:36
what if the group has all nulls? should you still show null in the output? — Vamsi Prabhala
– Vamsi Prabhala, Commented Mar 23, 2018 at 14:37
sorry for confusing text, thanks laurenz for proper reformulation. if all are nulls, then null could be included. Well should for my usecase, but I will be glad if I see both solutions. — Martin Mucha
– Martin Mucha, Commented Mar 23, 2018 at 15:02
Do you need a solution that works both in Oracle and PostgreSQL? If so, do you want it to work in particular versions of the DBMSs? — Thorsten Kettner
– Thorsten Kettner, Commented Mar 23, 2018 at 15:23

wolfrevokcats · Accepted Answer · 2018-03-23 17:07:42Z

5

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

edited Mar 23, 2018 at 17:07

answered Mar 23, 2018 at 14:41

wolfrevokcats

2,1101 gold badge14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andomar Over a year ago

Interesting! The OP's VALUES (1,2,1),(1,1,2),(1,3,3) suggests he is not using Oracle

Thorsten Kettner Over a year ago

The first query does not work, because you are accessing the last record regardless of the value being null or not. The second query is a good solution. A pity that LAST_VALUE is not yet available as an aggregate function (LAST_VALUE WITHIN GROUP).

Martin Mucha Over a year ago

@Andomar I'm looking for answer for postgres/oracle, example from question was taken from my postgress shell. Oracle solution is indeed welcomed, I just have to test all of suggestions here.

wolfrevokcats Over a year ago

@ThorstenKettner you've been right, I've fixed my answer.

Martin Mucha Over a year ago

Thanks, this solution(I tested the first one) seems to work for multiple columns, which at some row can be null, and seems to correctly find max number for each column. Also if all rows are null for given column, null is returned. Perfect for me. And I totally understand it, except for the first line (joke) ~ actually I have no idea how it works. But it works!

Andomar · Accepted Answer · 2018-03-23 14:24:50Z

3

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.

answered Mar 23, 2018 at 14:24

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

Martin Mucha Over a year ago

I tried something like this, but I thew that out, because there are multiple columns like 'num' which makes where clause impossible to write ... (well I assume).

Laurenz Albe · Accepted Answer · 2018-03-23 14:53:46Z

0

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;

answered Mar 23, 2018 at 14:53

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

1 Comment

Martin Mucha Over a year ago

same as for Andomar above -- there are multiple columns like num, which has to have same treatment. So where clause would have to be like: where num1 is not null or num2 is not null ... which IIUC wouldn't work, allowing nullvalued num1 into the result set (for row with non-null num2)

Thorsten Kettner · Accepted Answer · 2018-03-23 15:42:55Z

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

Collectives™ on Stack Overflow

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

example

4 Answers 4

5 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

example

4 Answers 4

5 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related