On 2/8/16, Alban Hertroys <haramrae@xxxxxxxxx> wrote: > >> On 08 Feb 2016, at 20:05, Johannes <jotpe@xxxxxxxxx> wrote: >> >> select id, col1, col2, ... from t0 where id = (select max(id) from t0 >> where col1 = value1 and col2 = value2 and …); >> >> select col1 from t1 where t0_id = (select max(id) from t0 where col1 = >> value1 and col2 = value2 and …); > > select t0.id, t0.col1, t0.col2, t0…., t1.col1 > from t0 > join t1 on (t1.t0_id = t0.id) > group by t0.id, t0.col1, t0.col2, t0…., t1.col1 > having t0.id = max(t0.id); > > Low complexity and works with any number of rows from t0 (as does Adrian's > solution, btw). I think it fully ruins speed at all. Try to create tables, insert at least 100000 rows into each of them (note that cardinality between them is 1:m) and see EXPLAIN of your query. You are joining two big tables, sort and group a resulting table and remove most rows to fit into one statement... > I'm not sure what you mean by "copying of columns" in your reply to Adrian's > solution, but I don't think that happens here. In the original letter the first query returns one row: "(id, col1, col2)", and the second one returns rows "(val1), (val2), (val3), ..." (values of the t1.col1). If you use joining, you get rows: (id, col1, col2, val1) (id, col1, col2, val2) (id, col1, col2, val3) ... where values of the first three columns are the same. > > Alban Hertroys -- Best regards, Vitaly Burovoy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
