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=pro.cod_produccion and mov.cod_movimiento= (select max(mov2.cod_movimiento) from movimientos mov2 where mov2.cod_produccion=pro.cod_produccion) where mov.cod_ubicacion=5 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=pro.cod_produccion inner join movimientos mov2 on mov2.cod_produccion=pro.cod_produccion group by mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida, mov1.cod_movimiento having mov1.cod_movimiento=max(mov2.cod_movimiento) and mov1.cod_ubicacion=5 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ésar A. León Mansilla./
