[NEW] ORDER BY in aggregative functions First()/Last()

SQL Standard allows to SELECT only fields mentioned in the GROUP BY and expressions based on aggregative functions. You cannot SELECT a normal field. But sometimes you may very want to do this. Question is what to do in this case.

In this new Valentina Wiki article, we have describe in detail this problem and gave THREE solutions. The third solution is new for v5.0 and it works x400/ x50 times faster of the first two correspondently in tests on the database of our customer.

The third solution, uses idea from ORACLE database actually: FIRST()/LAST() functions with own ORDER BY to be used inside of each group. It seems mySQL and PostgreSQL do not have any way to resolve this task in such effective way.

Published by

Ruslan Zasukhin

VP Engineering and New Technology Paradigma Software, Inc