I have two tables apps
and builds
apps
x
app_guid
name
state
created_at
updated_at
foundation
builds
guid
app_guid
app_name
state
created_at
updated_at
foundation
One app can have multiple builds, but I want to return only most recently updated row of builds.
SELECT
a.name as AppName,
a.state as AppState,
a.created_at as AppCreatedAt,
a.updated_at as AppUpdatedAt,
a.foundation as AppFoundation,
b.state as BuildState,
b.created_by_email,
b.created_by_name
FROM
apps as a
JOIN
builds as b ON a.app_guid = (SELECT TOP 1
b.state as BuildState,
b.created_by_email,
b.created_by_name
FROM
builds AS b
WHERE
b.app_guid = a.app_guid
ORDER BY
b.updated_at DESC)
WHERE
b.state != 'FAILED'
AND a.foundation = 2
AND a.deleted_at IS NULL
ORDER BY
a.updated_at DESC
This query is not returning anything.
Advertisement
Answer
Use CROSS APPLY
:
SELECT . . .
FROM apps a CROSS APPLY
(SELECT TOP (1) b.*
FROM builds b
WHERE a.app_guid = b.app_guid
ORDER BY b.updated_at DESC
) b
WHERE . . .