Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8C81E9FB1BE for ; Sun, 4 Feb 2007 19:30:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 91061-08 for ; Sun, 4 Feb 2007 19:30:11 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id E032E9FA589 for ; Sun, 4 Feb 2007 19:30:10 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so1963862nfb for ; Sun, 04 Feb 2007 15:30:09 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=Vh1G0r5u2vl/11p8Nor35157KQKY5T3sjfNkeF/ikA60pmPoa930bu1xpE4dVNb5R19pghnZ6oX9BpNkvgYMtGmJwD0UlZCYzIRdIG9eGhkmThS7dfOhHQp045xxR5aEIj94+XgmMZ6RREPTw8JioD3SA5aO6sVi2UE43HZRzis= Received: by 10.49.20.5 with SMTP id x5mr1529356nfi.1170631808942; Sun, 04 Feb 2007 15:30:08 -0800 (PST) Received: by 10.48.245.14 with HTTP; Sun, 4 Feb 2007 15:30:08 -0800 (PST) Message-ID: Date: Sun, 4 Feb 2007 20:30:08 -0300 From: "=?ISO-8859-1?Q?C=E9sar_Antonio_Le=F3n_Mansilla?=" To: pgsql-sql@postgresql.org Subject: Getting associated columns with max() without subquery MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_63025_14755015.1170631808903" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/43 X-Sequence-Number: 27377 ------=_Part_63025_14755015.1170631808903 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, this is my first post, sorry for my english, I'm chilean and my first language is spanish. I don't know if somebody got this solved, but here we go: I was surfing the net for the fastest query that let me to get the max movement from a production plus associated columns related to this production, everywhere I got "use a subquery to get the max movement", so, I did write: select mov.cod_produccion, mov.cod_ubicacion, mov.ind_salida, mov.cod_movimiento as max_movimiento from producciones pro inner join movimientos mov on mov.cod_produccion=3Dpro.cod_produccion and mov.cod_movimiento=3D (select max(mov2.cod_movimiento) from movimientos mov2 where mov2.cod_produccion=3Dpro.cod_produccion) where mov.cod_ubicacion=3D5 and not mov.ind_salida that was a good answer but too slow... :( My database has 71727 rows in "producciones" table and 112266 rows in "movimientos" table, then the response time for this query was 31531ms, getting 587 rows as result. Looking the help, and understanding which is the difference between WHERE and HAVING, I try to optimize that query to get a better response time and got it: select mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida, max( mov2.cod_movimiento) as max_movimiento from producciones pro inner join movimientos mov1 on mov1.cod_produccion=3Dpro.cod_produccion inner join movimientos mov2 on mov2.cod_produccion=3Dpro.cod_produccion group by mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida, mov1.cod_movimiento having mov1.cod_movimiento=3Dmax(mov2.cod_movimiento) and mov1.cod_ubicacion=3D5 and not mov1.ind_salida order by mov1.cod_produccion the response time now was 297ms, even with "order by", getting the same 587 rows as result. the table fields are: movimientos ------------------ cod_movimiento (pk) (serial) cod_produccion (fk) (int4) cod_ubicacion (fk) (int4) fec_movimiento (timestamp) ind_salida (bool) producciones -------------------- cod_produccion (pk) (serial) cod_dia_laboral (fk) (int4) cod_producto (fk) (int4) fec_produccion (timestamp) pso_produccion (numeric(10,2)) My test computer is: Hardware: Sempron 2400+, 512Mb. RAM, 80Gb. 7200RPM. Software: Windows XP Professional, PostgreSQL (of course :). This look like standard SQL and would work in any DBMS, so my question is: Are really those querys getting the same results? Thank in advance for your answer. Good bye. Greeting from Chile. C=E9sar A. Le=F3n Mansilla./ ------=_Part_63025_14755015.1170631808903 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, this is my first post, sorry for my english, I'm chilean and m= y first language is spanish.
I don't know if somebody got this solve= d, but here we go:

I was surfing the net for the fastest query that&= nbsp;let me to get the max movement from = ;a production
plus associated columns related to this = production, everywhere I got "use a subquery to ge= t the
max movement", so, I did write:

  sele= ct mov.cod_produccion, mov.cod_ubicacion, mov.ind_salida, mov.cod_movimient= o as max_movimiento
  from producciones pro
  inne= r join movimientos mov
  on mov.cod_produccion=3Dpro.cod_produ= ccion and mov.cod_movimiento=3D
  (select max(mov2.cod_movimie= nto) from movimientos mov2 where mov2.cod_produccion=3Dpro.cod_produccion )
  where mov.cod_ubicacion=3D5 and not mov.ind_salida

= that was a good answer but too slow... :(
My database has 71727 rows in = "producciones" table and 112266 rows in "movimientos" t= able, then the
response time for this query was 31531ms, getting 587 rows as result.Looking the help, and understanding which is the difference between WHERE= and HAVING, I try to optimize that 
query to get a better respon= se time and got it:

  select mov1.cod_produccion, mov1.cod_ubicacion, mov1.in= d_salida, max(mov2.cod_movimiento) as max_movimiento
  from pr= oducciones pro
  inner join movimientos mov1
  on= mov1.cod_produccion=3Dpro.cod_produccion
  inner join movimientos mov2
  on mov2.cod_prod= uccion=3Dpro.cod_produccion
  group by mov1.cod_produccion, mo= v1.cod_ubicacion, mov1.ind_salida, mov1.cod_movimiento
  havin= g mov1.cod_movimiento=3Dmax(mov2.cod_movimiento ) and mov1.cod_ubicacion=3D5 and not mov1.ind_salida
  order b= y mov1.cod_produccion

the response time now was 297ms= , even with "order by", getting the same 587 rows as result.
<= br>the table fields are:

movimientos
------------------
cod_movimiento   (pk= ) (serial)
cod_produccion    (fk) (int4)
cod_ubicacion=      (fk) (int4)
fec_movimiento   &n= bsp;(timestamp)
ind_salida        &nb= sp;   (bool)

producciones
--------------------
cod_produccion    (pk) (serial)<= br>cod_dia_laboral    (fk)  (int4)
cod_producto &nbs= p;     (fk)  (int4)
fec_produccion  &= nbsp;  (timestamp)
pso_produccion    (numeric(1= 0,2))

My test computer is:=20
  Hardware: Sempron 2400+, 512Mb. RAM, 80Gb. 7200RPM.
&nb= sp; Software: Windows XP Professional, PostgreSQL (of course :).
This look like standard SQL and would work in any DBMS, so my question is= : Are really those querys getting=20
the same results?

Thank in advance for your&= nbsp;answer.

Good bye.
Greeting from Chile. <= br>
C=E9sar A. Le=F3n Mansilla./
------=_Part_63025_14755015.1170631808903--