CODEWITHSUNDEEP
sqlsql-serverjoin
Coldorak
Coldorak

Reputation: 33

SQL join with condition of last item of the right table

Sorry if the title is not really understandable, I've trouble even expressing it!

To simplify, I have two tables in a SQL Server 2012 database: one with requests and one with actions related to these requests (it's from a service manager software)

Table REQUESTS            Table ACTIONS
+------+-------+---+      +---------+------+--------+
|Req_ID|RFC_Num|...|      |Action_ID|Req_ID|Group_ID|
+------+-------+---+      +---------+------+--------+
| 1    | I01   |...|      |  a      | 1    | 10     |
| 2    | I02   |...|      |  b      | 1    | 20     |
| 3    | I03   |...|      |  c      | 1    | 38     |
| 4    | I04   |...|      |  d      | 1    | 38     |
+------+-------+---+      |  e      | 2    | 10     |
                          |  f      | 2    | 38     |
                          |  g      | 2    | 20     |
                          |  h      | 3    | 38     |
                          |  i      | 4    | 10     |
                          +---------+------+--------+

I want to be able to select all requests where the last action for that request has group_id = 38, so it should return requests with req_id is 1 and 3, but not req2 as the last action is made by another group than 38 nor req4 as group 38 is not involved.

I've tried things like

select *
from REQUEST r
inner join ACTION a 
    on (
        (r.REQUEST_ID = a.REQUEST_ID) 
        and (select group_id 
            from ACTION a2 
            where a2.action_id = a.action_id
            ).GROUP_ID = 38
        )

but I can't figure out how to put the "max(action_id)" (or select top 1 with an order by desc) and I don't really understand how to use things like having.

I've also tried requests like this one:

select *
from REQUEST r
inner join ACTION a 
    on (
        (r.REQUEST_ID = a.REQUEST_ID) 
        and (select top 1 a2.action_id, a2.group_id 
            from ACTION a2 
            where a2.action_id = a.action_id
            group by a2.ACTION_ID
            order by a2.action_id desc
            ).GROUP_ID = 38
        )

But I get these two errors:

Msg 8120, Level 16, State 1, Line 6
Column ACTION.GROUP_ID is not valid in the selection list because it's not contained in an aggregation function or in the GROUP BY clause.

Msg 116, Level 16, State 1, Line 11
Only one expression can be specified in the selection list when the subquery is not introduced by EXISTS.

Thank you for you help!

Upvotes: 3

Views: 76

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I would be inclined to use apply:

select r.*, a.*
from request r cross apply
     (select top 1 a.*
      from action a
      where a.request_id = a.request_id
      order by a.action_id desc
     )
where a.group_id = 38;

This assumes that "last action" is based on the action_id column. Whatever the definition is is what the order by uses.

Upvotes: 0

fafl
fafl

Reputation: 7385

Your query looks longer than it needs to be. Try this:

SELECT *
FROM requests r
WHERE (
    SELECT TOP 1 Group_ID
    FROM actions
    WHERE Req_ID = r.Req_ID
    ORDER BY Action_ID DESC
) = 38

SQL Fiddle

Upvotes: 2

sticky bit
sticky bit

Reputation: 37487

You could join the tables, only keeping records WHERE Group_ID = 38 and no other action for this request exists with a greater Action_ID.

SELECT r.*
       FROM REQUESTS r
            INNER JOIN ACTIONS a
                       ON a.Req_ID = r.Req_ID
       WHERE a.Group_ID = 38
             AND NOT EXISTS (SELECT *
                                    FROM ACTIONS ai
                                    WHERE ai.Req_ID = a.Req_ID
                                          AND ai.Action_ID > a.Action_ID);

SQL Fiddle

Upvotes: 0

NiVeR
NiVeR

Reputation: 9806

Your join column is wrong, you are using:

(r.REQUEST_ID = a.ACTION_ID)

but should be:

(r.REQUEST_ID = a.REQUEST_ID)

Upvotes: 0

Related Questions