Database
Enthusiast

## 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

 1 1 ? 1 1 2 3 1 1 3 ? 0 1 4 ? 0 1 5 6 1 1 6 8 1 1 7 2 1 1 8 ? 0 1 9 1 1

Expected output:

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

 1 1 ? 1 ? 1 2 3 1 3 1 3 ? 0 3 1 4 ? 0 3 1 5 6 1 6 1 6 8 1 8 1 7 2 1 2 1 8 ? 0 2 1 9 1 1 1

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

 1 1 ? 1 1 1 2 3 1 2 1 3 ? 0 2 1 4 ? 0 2 1 5 6 1 5 1 6 8 1 6 1 7 2 1 7 1 8 ? 0 7 1 9 1 1 9

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
Highlighted

## 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 2
Highlighted

## 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```
Enthusiast

## 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!!