Lists: | pgsql-general |
---|
From: | Emanuel Calvo Franco <postgres(dot)arg(at)gmail(dot)com> |
---|---|
To: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | array_agg + limit |
Date: | 2010-11-27 12:59:37 |
Message-ID: | AANLkTik1mcZCjWmZd+LKTT5Nk2_ELXBsjPgXJFUKCqb8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | Emanuel Calvo Franco <postgres(dot)arg(at)gmail(dot)com> |
Cc: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: array_agg + limit |
Date: | 2010-11-27 13:29:52 |
Message-ID: | 4CF107D0.6020307@lelarge.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Emanuel Calvo Franco <postgres(dot)arg(at)gmail(dot)com> |
Cc: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: array_agg + limit |
Date: | 2010-11-27 13:31:18 |
Message-ID: | AANLkTimCKhaCpar5O2f53W7dzBQps-5Pf_yRWyYupshc@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)arg(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From: | Lew <noone(at)lewscanon(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: array_agg + limit |
Date: | 2010-11-27 15:24:03 |
Message-ID: | icr7qv$ip1$1@news.albasani.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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
From: | Emanuel Calvo Franco <postgres(dot)arg(at)gmail(dot)com> |
---|---|
To: | Lew <noone(at)lewscanon(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: array_agg + limit |
Date: | 2010-11-27 18:26:24 |
Message-ID: | AANLkTi=fv_x2vGTYnH222dW76Sx6pEeoqiMtQeMdwm56@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2010/11/27 Lew <noone(at)lewscanon(dot)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