CODEWITHSUNDEEP
sqlsql-server
Sam
Sam

Reputation: 545

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.

Upvotes: 0

Views: 78

Answers (2)

cte6
cte6

Reputation: 637

There are a few problems with your query:

  1. You cannot join the column a.app_guid with the result of a query that returns multiple columns

  2. You should use the same alias for the table in the outer and inner join. Always use different aliases to be clear which table you are referencing.

    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 = b.app_guid WHERE b.state != 'FAILED' AND a.foundation = 2 AND a.deleted_at IS NULL and b.updated_at = (SELECT TOP 1 b2.updated_at FROM builds AS b2 WHERE b2.app_guid = b.app_guid ORDER BY b2.updated_at DESC) ORDER BY a.updated_at DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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

Upvotes: 2

Related Questions