Hi, this is my first post, sorry for my english, I'm chilean and my first language is spanish.<br />I don't know if
somebodygot this solved, but here we go:<br /><br />I was surfing the net for the fastest query that let me to
get the max movement from a production<br /> plus associated columns related to this production, everywhere I got "use
asubquery to get the<br />max movement", so, I did write: <br /><br /> select mov.cod_produccion, mov.cod_ubicacion,
mov.ind_salida,mov.cod_movimiento as max_movimiento<br /> from producciones pro<br /> inner join movimientos mov<br
/> onmov.cod_produccion=pro.cod_produccion and mov.cod_movimiento=<br /> (select max(mov2.cod_movimiento) from
movimientosmov2 where mov2.cod_produccion=pro.cod_produccion )<br /> where mov.cod_ubicacion=5 and not
mov.ind_salida<br/><br />that was a good answer but too slow... :(<br />My database has 71727 rows in "producciones"
tableand 112266 rows in "movimientos" table, then the <br />response time for this query was 31531ms, getting 587 rows
asresult.<br />Looking the help, and understanding which is the difference between WHERE and HAVING, I try to optimize
that <br/>query to get a better response time and got it: <br /><br /> select mov1.cod_produccion, mov1.cod_ubicacion,
mov1.ind_salida,max(mov2.cod_movimiento) as max_movimiento<br /> from producciones pro<br /> inner join movimientos
mov1<br/> on mov1.cod_produccion=pro.cod_produccion <br /> inner join movimientos mov2<br /> on
mov2.cod_produccion=pro.cod_produccion<br/> group by mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida,
mov1.cod_movimiento<br/> having mov1.cod_movimiento=max(mov2.cod_movimiento ) and mov1.cod_ubicacion=5 and not
mov1.ind_salida<br/> order by mov1.cod_produccion<br /><br />the response time now was 297ms, even with "order by",
gettingthe same 587 rows as result.<br /><br />the table fields are: <br /><br />movimientos<br />------------------<br
/>cod_movimiento (pk) (serial)<br />cod_produccion (fk) (int4)<br />cod_ubicacion (fk) (int4)<br
/>fec_movimiento (timestamp)<br />ind_salida (bool)<br /><br />producciones <br />--------------------<br
/>cod_produccion (pk) (serial)<br />cod_dia_laboral (fk) (int4)<br />cod_producto (fk) (int4)<br
/>fec_produccion (timestamp)<br />pso_produccion (numeric(10,2))<br /><br />My test computer is: <br
/> Hardware:Sempron 2400+, 512Mb. RAM, 80Gb. 7200RPM.<br /> Software: Windows XP Professional, PostgreSQL (of course
:).<br/><br />This look like standard SQL and would work in any DBMS, so my question is: Are really those querys
getting<br />the same results?<br /><br />Thank in advance for your answer.<br /><br />Good bye.<br
/>Greeting from Chile. <br/><br />César A. León Mansilla./<br />