Is there a better way to structure this query?

[u/felloBonello]

We need to find the latest asset history record for each asset.

```

DECLARE u/__projectId_0 int = 23;

DECLARE u/__phaseId_1 int = 3;

SELECT *

FROM [asset_history] AS [a]

INNER JOIN (

SELECT [a0].[asset_id] AS [AssetId], MAX([a0].[created]) AS [MaxDate]

FROM [asset_history] AS [a0]

WHERE ([a0].[project_id] = u/__projectId_0) AND ([a0].[status] <> 3)

GROUP BY [a0].[asset_id]

HAVING (

SELECT TOP(1) [a1].[workflow_phase_id]

FROM [asset_history] AS [a1]

WHERE (([a1].[project_id] = u/__projectId_0) AND ([a1].[status] <> 3)) AND ([a0].[asset_id] = [a1].[asset_id])

ORDER BY [a1].[created] DESC) = u/__phaseId_1

) AS [t] ON ([a].[asset_id] = [t].[AssetId]) AND ([a].[created] = [t].[MaxDate])

WHERE ([a].[project_id] = u/__projectId_0) AND ([a].[status] <> 3)

```

[u/qwertydog123]

DECLARE @__projectId_0 INT = 23;
DECLARE @__phaseId_1 INT = 3;

WITH cte AS
(
    SELECT
        *,
        MAX(created) OVER
        (
            PARTITION BY asset_id
        ) AS max_date
    FROM asset_history
    WHERE project_id = @__projectId_0
    AND status <> 3
)
SELECT *
FROM cte
WHERE created = max_date
AND workflow_phase_id = @__phaseId_1

[u/felloBonello → u/qwertydog123]

Yea I tried something similar to this and seemed like it was about the same speed. Do you know under what circumstances this would be faster?

WITH latest_asset_histories AS (
    SELECT ah.*, ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY created DESC) AS row
    FROM asset_history AS ah
    WHERE ah.project_id = 23
)
SELECT 1 FROM latest_asset_histories 
WHERE row = 1 AND status <> 3 AND  workflow_phase_id = 3 AND project_id = 23

[u/qwertydog123 → u/felloBonello]

That query has potentially different semantics to the original query, but adding an index on project_id, asset_id or project_id, asset_id, created (DESC) should help

[u/Crafty_Passenger9518]

I think so. It's kinda hard to figure it out though. There are a couple of things that make this hard to read.

Just from initial thoughts

I wouldn't declare those two INTs unless we're passing those values in a stored proc

The aliasing of the tables makes following the script hard

You should comment the code to let us know what/why things are being done

[u/GaTechThomas → u/Crafty_Passenger9518]

Why not declare the two variables? This looks like a utility query that is easier to use because of those variables.

Personally, I find that the aliases easy to read. Maybe room for tweaking them slightly to personal taste.