SQL query to get top 1 (most recently updated)

I have two tables apps and builds

apps

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.

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 . . .

Advertisement

Answer