Обсуждение: array_agg + limit

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?


--
              Emanuel Calvo Franco
        DBA | www.emanuelcalvofranco.com.ar
Curso a distancia Nivel 1 Admin Postgres:
    http://www.postgresql-es.org/node/525

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

Hello

probably you want

select array_agg(patente) from (select patente from dia4.infaccion
ORDER BY fecha DESC LIMIT 6) x

Regards

Pavel

2010/11/27 Emanuel Calvo Franco <postgres.arg@gmail.com>:
> 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?
>
>
> --
>               Emanuel Calvo Franco
>         DBA | www.emanuelcalvofranco.com.ar
> Curso a distancia Nivel 1 Admin Postgres:
>     http://www.postgresql-es.org/node/525
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Emanuel Calvo Franco wrote:
> 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 [know] why it happens an[d] how [to] avoid it, but.... it's ok?
> Must work like this?

Yes and yes.  LIMIT applies to the number of rows, and that's how it's documented.
<http://www.postgresql.org/docs/9.0/interactive/queries-limit.html>

--
Lew

2010/11/27 Lew <noone@lewscanon.com>:
> Emanuel Calvo Franco wrote:
>>
>> 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 [know] why it happens an[d] how [to] avoid it, but.... it's ok?
>> Must work like this?
>

;) Thks

> Yes and yes.  LIMIT applies to the number of rows, and that's how it's
> documented.
> <http://www.postgresql.org/docs/9.0/interactive/queries-limit.html>
>
> --

You understood me. The form proposed by Guillaume:

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

Is the way i've talked about. My question was about the 'how it works'.

Regards,

--
              Emanuel Calvo Franco
        DBA | www.emanuelcalvofranco.com.ar
Curso a distancia Nivel 1 Admin Postgres:
    http://www.postgresql-es.org/node/525