It's not the best DB design but the query without the null test and the max aggregate should have worked. I am convinced there must be a bug exposed when doing nested sub queries on the same table and the bug may show itself the deeper you stack - stack meaning nested subselect on the same table. I am also convinced that I am completely insane and may be missing something very obvious like a noob.
Any help/comment highly appreciated in advance.
If you deign to provide a self-contained test case showing where the non-aggregated query provides bogus results while the aggregated and limited one does not we would be most greatful since we could then test whether what you are seeing exists in a release of PostgreSQL that is currently supported. And if the behavior is correct we would have concrete values that could be used the in the explanation of said behavior.
Don't expect us to be able to upgrade the quality of the discussion: If the best you can give us is phrases like "weird" and "bogus" to describe what you are seeing, and no explicit schema definitions, then they best I can say is that while this looks odd it is likely explainable and a direct function of the fact that "it's not the best DB design" and that because of such there are data anomalies that are potentially coming into play here.
Or its a bug - potentially one that has been fixed.
