Can PostgreSQL return a list of values with latter values overwriting previous ones?

user_12959903 0
Viewed 52

The table below represents decision events on the license of the given file.

I want to end up with a list of licenses of the file. If Removed column of the event is False, a license is added, but no more than one of each licenses for each file. If Removed column of the event is True, all the previous adding events for the particular license are overturned. An event made later it time takes precedence over previous ones.

For the events below, I want to return a list of [A, B]. License A has events add, remove, add, so it is returned. License B has events add, add, so it is returned, but not duplicated. License C has events add, add, remove, so it is not removed, as the latter removal nullifies both add-events.

Is this possible to achieve with a PostgreSQL query, or do I need to process data afterwards?

+------+---------+---------+
| Time | License | Removed |
+------+---------+---------+
|    1 | A       | False   |
|    2 | A       | True    |
|    3 | A       | False   |
|    4 | B       | False   |
|    5 | B       | False   |
|    6 | C       | False   |
|    7 | C       | False   |
|    8 | C       | True    |
+------+---------+---------+
greatest-n-per-group select postgresql sql
3 Answers

You could use distinct on in a subquery to filter on the last record per license, then filter out those that are removed:

select license
from (select distinct on (license) t.* from mytable t order by license, time desc) t
where not removed
user_106767160

If I understand it correctly you want the last event for every license. It is quite simple if you know how window functions work: you can partition your table using windows and then operate inside each window by sorting, etc. In this case you want to partition on the license, then sort on the time (descending) and finally select the most recent entry of each window:

SELECT "License", "Removed" FROM (
    SELECT *, rank() OVER (PARTITION BY "License" ORDER BY "Time" DESC)) X
 WHERE rank = 1

If you want to better understand how it works, try executing the inner SELECT by itself.

user_573340

You could use aggregation for this:

select license
from t
group by license
having max(time) = max(time) filter (where not removed);

The having clause is checking that the maximum time for the license is not "removed".

All three of the answers are quite reasonable. In general, distinct on is preferable in Postgres over row_number() for fetching one row. I offer this if you have a strict aversion to subqueries.

user_11440350
Related