Analytic query: latest record with a condition

Database

Analytic query: latest record with a condition

Hi,

 

I would like to grab a value from prior records based on that prior record's additional condition. For example, in below sample set, group/parition is "a", order of records is "b". Here I want to get value of "c" from "unbounded preceding to current row" wherever latest (as I said the order is by "b") "d = 1" exists.

 

SyntaxEditor Code Snippet

create volatile table ky (a integer, b integer, c integer, d integer);
INSERT INTO ky VALUES(1, 1, NULL, 1);
INSERT INTO ky VALUES(1, 2, 3, 1);
INSERT INTO ky VALUES(1, 3, NULL, 0);
INSERT INTO ky VALUES(1, 4, NULL, 0);
INSERT INTO ky VALUES(1, 5, 6, 1);
INSERT INTO ky VALUES(1, 6, 8, 1);
INSERT INTO ky VALUES(1, 7, 2, 1);
INSERT INTO ky VALUES(1, 8, NULL, 0);
INSERT INTO ky VALUES(1, 9, 1, 1);

 

  a b c d 1 2 3 4 5 6 7 8 9

11?1
1231
13?0
14?0
1561
1681
1721
18?0
1911

 

Expected output:

  a b c d derived_c 1 2 3 4 5 6 7 8 9

11?1?
12313
13?03
14?03
15616
16818
17212
18?02
19111

 

I've written a query that would give "b" of the prior row from which "c" values needs to be obtained. But I need to get value "c" directly instead other row number.

 

SyntaxEditor Code Snippet

sel a, b, c, d, max(case when d = 1 then b end) over(partition by a order by b  rows between unbounded preceding and  current row) derived_bfrom ky
order by b

 

  a b c d derived_b 1 2 3 4 5 6 7 8 9

11?11
12312
13?02
14?02
15615
16816
17217
18?07
19119

 

I've been trying to accomplish all this in single sql without additional joins. Using "derived_b", I could self join ( on 'a" and "b" with "a" and "derived_b") to get value of "c" but I'm wondering if there are better options from smarter brains.

 

Any help if greatly appreciated.

 

Karteek


Accepted Solutions
N/A

Re: Analytic query: latest record with a condition

This is a simple task for LAST_VALUE:

 

SELECT a, b, c, d, 
Last_Value(CASE WHEN d = 1 THEN c END IGNORE NULLS) 
Over(PARTITION BY a 
     ORDER BY b 
     ROWS Unbounded Preceding) 
FROM ky
ORDER BY b
1 ACCEPTED SOLUTION
2 REPLIES
N/A

Re: Analytic query: latest record with a condition

This is a simple task for LAST_VALUE:

 

SELECT a, b, c, d, 
Last_Value(CASE WHEN d = 1 THEN c END IGNORE NULLS) 
Over(PARTITION BY a 
     ORDER BY b 
     ROWS Unbounded Preceding) 
FROM ky
ORDER BY b

Re: Analytic query: latest record with a condition

How stupid I'm :). I was playing with a very close statement but without using "ignore nulls". Appreciate your quick response! It helped!!