How to return latest rows by date for each field

Question

I'm on postgres 9.4. I want to query my adjustments table for the latest adjustment for each field. Some adjustments can adjust multiple fields.

Example 1

+---------------+-------------+----------------+---------------+
| available_qty | on_hand_qty |  backstock_qty |     date      |
+---------------+-------------+----------------+---------------+
| null          | 2           | null           | 2018-01-03    |
| 1             | null        | 10             | 2018-01-04    |
| null          | null        | 2              | 2018-01-01    |
| 3             | 3           | null           | 2018-01-02    |
+---------------+-------------+----------------+---------------+

...and querying for the latest adjustment for each field would result in:

+---------------+-------------+----------------+---------------+
| available_qty | on_hand_qty |  backstock_qty |     date      |
+---------------+-------------+----------------+---------------+
| null          | 2           | null           | 2018-01-03    |
| 1             | null        | 10             | 2018-01-04    |
+---------------+-------------+----------------+---------------+

Example 2

+---------------+-------------+----------------+---------------+
| available_qty | on_hand_qty |  backstock_qty |     date      |
+---------------+-------------+----------------+---------------+
| null          | 2           | null           | 2018-01-03    |
| 1             | null        | null           | 2018-01-04    |
| null          | null        | 2              | 2018-01-01    |
| null          | null        | 3              | 2018-01-02    |
| 3             | null        | null           | 2018-01-02    |
| null          | 4           | null           | 2018-01-01    |
+---------------+-------------+----------------+---------------+

...and querying for the latest adjustment for each field would result in:

+---------------+-------------+----------------+---------------+
| available_qty | on_hand_qty |  backstock_qty |     date      |
+---------------+-------------+----------------+---------------+
| null          | 2           | null           | 2018-01-03    |
| 1             | null        | null           | 2018-01-04    |
| null          | null        | 3              | 2018-01-02    |
+---------------+-------------+----------------+---------------+

I can achieve this with a lot of UNION queries:

(SELECT * FROM adjustments WHERE available_qty IS NOT NULL ORDER BY date DESC LIMIT 1)
UNION
(SELECT * FROM adjustments WHERE on_hand_qty IS NOT NULL ORDER BY date DESC LIMIT 1)
UNION
(SELECT * FROM adjustments WHERE backstock_qty IS NOT NULL ORDER BY date DESC LIMIT 1)

...but is this achievable with one query? In reality, there can be lots of fields here. Thanks in advance!

Try this one: https://stackoverflow.com/a/20748872/11610186 and in your case: http://www.sqlfiddle.com/#!17/f1a44/5 — Grzegorz Skibinski, Sep 08 '19 at 16:41
@GrzegorzSkibinski Thanks this is getting close! Is there any way to also return the date values for each corresponding field? — axsuul, Sep 08 '19 at 17:20

Grzegorz Skibinski · Answer 1 · 2019-09-08T21:28:53.327

Ok, so this is what you look for: (the order is - field, then date when it was latest adjusted)

select 
   first_value(available_qty) over (order by available_qty is null, date desc), 
   first_value(date) over (order by available_qty is null, date desc), 
   first_value(on_hand_qty) over (order by on_hand_qty is null, date desc),
   first_value(date) over (order by on_hand_qty is null, date desc),
   first_value(backstock_qty) over (order by backstock_qty is null, date desc),
   first_value(date) over (order by backstock_qty is null, date desc)
from
   adjustment
limit 1

And updated SQL fiddle

How to return latest rows by date for each field

Example 1

Example 2

1 Answers1