Обсуждение: Re agregates

>
> is there a better way to get the max - 1?
> I could loop through and run the queriy for each customer but the cost
> on ten thousand quesries is rather high also?
>
You can directly get the "max - 1" with "order by desc" and "limit" and "offset".
It's worth a try whether that is faster (I would suspect that it is O(n*log(n))
compared to O(n^2) in your "max from max" query).

Christoph Dalitz

Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
> You can directly get the "max - 1" with "order by desc" and "limit"
> and "offset".  It's worth a try whether that is faster (I would
> suspect that it is O(n*log(n)) compared to O(n^2) in your "max from
> max" query).

Given an index on the thing(s) being ordered by, it should be more like
O(log(n)) time: the system will only have to go to the right place in the
index (taking O(log(n)) for a btree search) and then step two index
entries (constant time).

It may not be practical to index this query, but if it is, the LIMIT
method will blow the doors off anything that involves MAX().

            regards, tom lane