0

I'am searching for the best way to filter a table by a MAX date. Is it better to find with a select in the where

 SELECT *
 FROM
     table1 foo
 WHERE
     foo.date_tmp = 
        (
            SELECT
                MAX (tmp.date_tmp ) 
            FROM
                table2 tmp 
            WHERE
                tmp.table_id = foo.id
        )

Or make it with a with

WITH tmp_max_date AS 
(
    SELECT
        tmp.table_id,
        MAX(date_tmp) date_tmp 
    FROM
        table2 tmp
    GROUP BY
        tmp.table_id
)
SELECT foo.*
FROM
    table1 foo
    INNER JOIN tmp_max_date tmd ON
        tmd.table_id = foo.id
        AND tmd.date_tmp = foo.date_tmp

Does anyone know the best way? IMO it's the second one but I'm not sure.

adex
  • 75
  • 10
  • 1
    Please show sample data (ideally as db fiddle or other reproducible example) and expected output. – Tomáš Záluský Oct 01 '21 at 14:56
  • You can certainly test on your own data to see which is better. You may find that there's no difference (although in this case there probably is, and you are likely correct: the second form will be faster). You can also write the second version as a semi-join - an `IN` condition instead of an actual join. –  Oct 01 '21 at 14:59
  • Does this answer your question? [High performance approach to greatest-n-per-group SQL query](https://stackoverflow.com/questions/9485124/high-performance-approach-to-greatest-n-per-group-sql-query) – astentx Oct 01 '21 at 15:26

1 Answers1

0

May be this:

select foo.id, 
max(foo.date_tmp) 
keep(dense_rank last order by foo.date_tmp) max_date_tmp
from table 1 foo, table 2 tmp
where foo.id = tmp.table_id
group by foo.id
Andrew Klimov
  • 126
  • 8
  • This is not equivalent to the OP's query, if there are ties for max `date_tmp` for some id's. The OP's query returns all those rows; yours returns only one row. Moreover the OP's query returns all the columns, yours only returns three columns. Definitely not "this". –  Oct 02 '21 at 15:10