On 2020-05-26 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 have studied the above SELECT, and I now more or less understand it. I
would not have come up with that unaided, so many thanks.
I tried DISTINCT ON, and it was very efficient, but unfortunately that
is not supported by SQL Server or sqlite3.
Then I came up with this alternative, which works on all three platforms
and seems a bit faster than the above -
SELECT a.row_id FROM (
SELECT row_id,
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
) as a
WHERE a.row_num = 1
Do you see any problem with this?
Thanks
Frank
