Обсуждение: 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