Can I avoid joining the same table multiple times?-CodePudding

Is there a way to improve the following query? I would need an optimized version of the following query. The reason I'm joining the Date_Table multiple times is because the ID and date_value columns are not in ascending order. ie ID = 1, date_value = '2022-09-07'; ID = 2, date_value = '2022-02-02'; ID = 3, date_value = '2022-11-12';

Sample data:

The maximum Date from the Agreements table is calculated based on the Date_Table.date_value column. The query will only return a row. In this case, the row highlighted in green will be the result.

Thank you so much!

SELECT * FROM Agreement
WHERE
  dim_date_id = (
                  SELECT
                    Date_Table.ID
                  FROM (
                        SELECT
                          MAX(Date_Table.date_value) AS date_value
                        FROM Agreement
                        INNER JOIN Date_Table
                          ON Agreement.DIM_DATE_ID = Date_Table.ID
                        ) AS last_day
                  INNER JOIN Date_Table
                    ON last_day.date_value = Date_Table.date_value
                );

CodePudding user response：

If Agreement is a large table, you should first find all the distinct date_ids, then join it to Date_Table. Also use a rank() windowing function to find the id of the most recent record:

Select Agreement.* From Agreement Inner Join (
    Select ID From (
        Select Date_Table.ID
            ,rank() Over (Order by Date_Table.date_value desc) as recent
        From Date_Table Inner Join (
            Select Distinct Dim_Date_ID as ID From Agreement
        ) A On A.ID=Date_Table.ID
    ) where recent=1
) X On Agreement.DIM_DATE_ID = X.ID

On first glance this looks just as complicated as your original query. But it quickly reduces the Agreement results to only a list of date ids, and especially if that field is indexed it is a fast query. Date_Table is then Inner Joined to find the best (most recent) Date_Value using a rank() function. The whole thing is filtered to retain only one record, the most recent, and that date_id is used to filter Agreement.

Again, I recommend that you index Agreement.Dim_Date_ID to make this query perform well.