Re: array_agg + limit - Mailing list pgsql-general

From Guillaume Lelarge
Subject Re: array_agg + limit
Date
Msg-id 4CF107D0.6020307@lelarge.info
Whole thread Raw
In response to array_agg + limit  (Emanuel Calvo Franco <postgres.arg@gmail.com>)
List pgsql-general
Le 27/11/2010 13:59, Emanuel Calvo Franco a écrit :
> I'm having this 'issue':
>
> coches=# select array_agg(patente order by fecha desc) from
> dia4.infraccion limit 6;
>
>                                array_agg
>
>
-------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------
>
{565538,726668,639414,739391,674212,941061,636533,981958,999057,743729,548494,923939,841427,454574,363754,670514,566266,251071,852043,1537
> 11,952549,129124,446671,476345,168905,308274,404812,501024,738098,636071}
> (1 row)
>
>
> I now why it happens an how avoid it, but.... it's ok? Must work like this?
>

The result is OK, but is probably not what you wanted to get. If you
want to limit the number of items in the array, you should limit before
aggregating. Something like this might work:

SELECT array_agg(patente ORDER BY fecha DESC)
FROM
  (SELECT * FROM dia4.infraccion ORDER BY fecha DESC LIMIT 6) tmp;

I didn't check, but this should work.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

pgsql-general by date:

Previous
From: Emanuel Calvo Franco
Date:
Subject: array_agg + limit
Next
From: Pavel Stehule
Date:
Subject: Re: array_agg + limit