CODEWITHSUNDEEP
sqlsql-serverdatabasecross-apply
Matt Larsuma
Matt Larsuma

Reputation: 1519

SQL Server CROSS APPLY

I'm trying to add in a CROSS APPLY to this query. I've never done one before, but a co-worker suggested it and it seems to make sense. I'm getting a syntax error though. Here's the query: 

            SELECT
                i.IncidentID,
                i.AccountID,
                i.IncidentTypeID,
                i.IncidentStateID,
                i.CreateDate,
                i.LastModifyDate,
                i.LastModifyUser,
                (
                    SELECT
                        COUNT(*) 
                    FROM
                        Actions a
                    WHERE
                        a.IncidentID = i.IncidentID
                ) AS ActionCount
            CROSS APPLY
            (
                SELECT TOP 1
                    a.IncidentStateID,
                    a.LastModifyDate
                FROM Actions a
                WHERE a.IncidentID = i.IncidentID
                ORDER BY a.LastModifyDate DESC
            )
            FROM
                Incidents i
            WHERE i.IncidentTypeID = 44
            AND i.IncidentStateID = 7
            AND i.CreateDate >= ?
            AND i.CreateDate < ?

Ultimately I need to get the a.IncidentStateID and a.LastModifyDate of the most recent "Action" that has an incident. This is why it is ordered by DESC and just selecting the top 1. Anyone see the syntax issue. The error just says General error: 20018 Incorrect syntax near the keyword 'ORDER'.. If I remove that it moves on to another piece of syntax and so on.

Upvotes: 0

Views: 150

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Apply should go after from clause :

SELECT i.IncidentID, i.AccountID, i.IncidentTypeID,
        t.IncidentStateID, i.CreateDate, i.LastModifyDate, t.LastModifyUser,
        (SELECT COUNT(*) 
         FROM Actions a
         WHERE a.IncidentID = i.IncidentID
        ) AS ActionCount
FROM Incidents i CROSS APPLY( 
     SELECT TOP (1) a.IncidentStateID, a.LastModifyDate
     FROM Actions a
     WHERE a.IncidentID = i.IncidentID
     ORDER BY a.LastModifyDate DESC
     ) t
WHERE i.IncidentTypeID = 44 AND i.IncidentStateID = 7 AND 
      i.CreateDate >= ? AND i.CreateDate < ?;

Upvotes: 2

Related Questions