SQL Server CROSS APPLY

Question

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.

This link might be helpful: https://stackoverflow.com/a/1139231/9236062 — Aura, Jun 15 '18 at 18:09
It's not clear to me why CROSS APPLY might be needed here. Is there a relationship between `Incidents` and `Actions`? CROSS APPLY will not give you the most recent Action for an Incident. CROSS APPLY is used when there is no key field shared between tables. — devlin carnate, Jun 15 '18 at 18:09
There is a relationship. I'm honestly not sure. My co-worker said it was the way to go. I do know that there is a One-to-Many relationship between Incidents and Actions, but as I'm only grabbing the most recent Action, duplicates would not be an issue. Definitely considering going another route. — Matt Larsuma, Jun 15 '18 at 18:11

Yogesh Sharma · Accepted Answer · 2018-06-15T18:14:19.143

2

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 < ?;

edited Jun 15 '18 at 18:14

answered Jun 15 '18 at 18:05

Yogesh Sharma

49,870
5
26
52

Ah. Thank you! I had it there and it wasn't working. It works now. What is the `t` for? – Matt Larsuma Jun 15 '18 at 18:09
1

Its an alias just how you have `Actions a` in your query. – Aura Jun 15 '18 at 18:10
Gotcha. Thanks! – Matt Larsuma Jun 15 '18 at 18:13

SQL Server CROSS APPLY

1 Answers1