d6 delta query – get the latest entry

I’m working on a query but I’m running into an issue with deltas and getting the latest info. If I have a table as such….

ID | fieldval | delta
1  |        1 |     0
1  |        0 |     1
2  |        1 |     0
2  |        0 |     1
3  |          |     0
3  |        0 |     1
3  |          |     2

I’d like to get results like:

ID | fieldval | delta
1  |        0 |     1
2  |        0 |     1
3  |        0 |     2

I want the latest delta that has a value per ID. I’m writing this in postgres but running into some issues. Any suggestions?

Answer

You can get the largest delta with a value using distinct on and filtering:

select distinct on (id) t.*
from t
where fieldval is not null
order by id, delta desc;

However this does not return exactly the results you have specified.