Skip to content
Advertisement

Calculated boolean column showing if column is newest

I have a table with a structure that more or less (I’ve simplified it for the question) looks like the following:

id (P.K.) creation_ts some_field
1 2021-08-19 foo
2 2021-08-18 foo
3 2021-08-17 foo
4 NULL bar
5 2021-01-01 bar
6 2021-01-02 bar

And I’m trying build a query to show a calculated column that per each row with the same value for "some_field" (erm… grouped by the value of "some_field" if you may?) would add an extra column is_newest showing which row is the newest.

id (P.K.) creation_ts some_field is_newest
1 2021-08-19 foo TRUE
2 2021-08-18 foo FALSE
3 2021-08-17 foo FALSE
4 NULL bar FALSE
5 2021-01-01 bar FALSE
6 2021-01-02 bar TRUE

The intention of this is to create a SqlAlchemy Hybrid property so we could quickly query things like “get me the newest record WHERE some_field = 'foo'

I imagine this must be some kind of CASE statement (at least that’s what I gather from this other S.O. answer, which looks pretty promising) but the best thing I can come with is something like:

JavaScript

but no: that seems pretty wrong to me (and it isn’t working, since some unit tests are failing), because it produces a query like…

JavaScript

… which doesn’t look right. Though to be honest, I’m not very sure what would “look right” (I’m quite a newbie with Postgres)

Any hint would be appreciated. Thank you in advance

Advertisement

Answer

You may use this window function query as native:

JavaScript

or (better) make a view out of it and then use the view instead of the table. Result:

JavaScript

Edit
Added coalesce to is_newest expression.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement