The solution proposed by J.D. will work, but it will have to sort the whole table three times and do quite an expensive DISTINCT.
As was alluded to in the comments, efficiently solving this task would require a window function similar to FIRST_VALUE with a filter, which, unfortunately, PostgreSQL...
@ypercubeᵀᴹ I put in your answer as a community wiki - removed my dog's (ahem... err... ahh) "dinner" of an answer. I did a performance analysis - you're no. 2 after Quassnoi, so not too bad? I answered the "decimal point" - I did put a lot of work in - went down many rabbit holes! :-) Got a correct answer tick for my troubles! Any input would be appreciated.
I have a question up on Overflow for a more generic regex - there's a Polish lad there who's pretty hot on regexes! Fingers crossed!
You know my answer is the same as Quassnoi's option 1, right?
Most important, how big was the table that you did performance analysis?
I would expect Quassnoi's option 2 to be the fastest in some cases.
(eg when the table has very few non-null values in one (or more) columns and they are all in the "start" of the table (low version numbers) while the rest being all nulls.)
@ypercubeᵀᴹ I did mention that the usual caveats apply! It was consistent though - but maybe it was the order in which they were performed? I can remove it if appropriate?
@ypercubeᵀᴹ - put yours at the bottom - results reversed! F*&^ that - I'll delete!
I have a subquery that will return 3 records, to later add the result of some calculations (using some functions).
When I am running the full query, Postgres is calculating a high number of expected rows and decides not to use the index. but when I use just the subquery, the index is used.
Plese ...
this question originally had screenshots of queries and plans, which wasn't nice (I asked the OP to add them as text), but still was useful to identify the problem.
The OP deleted the screenshots instead. The question will probably be more useful with screenshots than without them. Should I roll back the OP's edit and put the screenshots back?
