On Tue, 26 May 2020 at 23:41, Vik Fearing <vik@xxxxxxxxxxxxxxxxxxx> wrote: > > On 5/26/20 12:04 PM, David Rowley wrote: > > Since "my_table" is small, you'd likely be much better doing a manual > > rewrite of the query to join a subquery containing the required > > details from "my_table". It looks like you want the row_id from the > > latest tran_date for each fld_N column. So something like: > > > > SELECT a.row_id > > FROM table_1 b > > CROSS JOIN table_2 c > > CROSS JOIN table_3 d > > CROSS JOIN table_4 e > > LEFT OUTER JOIN ( > > SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date, > > ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY > > tran_date DESC) row_num > > FROM my_table > > WHERE deleted_id = 0 > > ) a ON a.fld_1 = b.row_id AND a.fld_2 = c.row_id AND a.fld_3 = > > d.row_id AND a.fld_4 = e.row_id AND a.row_num = 1; > > > > Should do it. You could also perhaps do something with DISTINCT ON > > instead of using ROW_NUMBER(). That might be a bit more efficient, but > > it's unlikely to matter too much since there are only 167 rows in that > > table. > > I would expect a lateral query to be better here. But that would put it back to executing the subquery 11088 times. I wrote it in a way to avoid that. David
