Using Partition and Join in MS SQL Server

Question

I have the following example set of data in SQL:

I need to select for RequestType = 1 and ToRoleID = 1 the maximum step where there is no record with other ToRoleID.

Example: If I filter by RequestType = 1 and ToRoleID = 1, this should give me only the row with Request_ID = 5.

Can I do it only with partition, JOIN?

Thank you.

I'm not exactly clear about the expected results. You're not showing a row where `Request_ID = 5` - do you mean row 5 in the result set shown, where `Request_ID = 18`? I also don't follow what you mean by "maximum step where there is no record with other `ToRoleID`. — LDMJoe, Jan 22 '16 at 19:43

score 0 · Accepted Answer · answered Jan 22 '16 at 19:42

0

This might be what you're wanting.

DECLARE @RequestType INT = 1,
        @ToRoleID INT = 1

SELECT  * 
FROM    (   SELECT  *,
                    MAX(ToRoleID) OVER (PARTITION BY Request_ID) MaxRole
            FROM    myTable
            WHERE   me.RequestType = @RequestType) mt
WHERE   MaxRole = @ToRoleID

answered Jan 22 '16 at 19:42

JamieD77

13,796
1
17
27

Thank you very much for you answer but when I change @ToRoleID the query should return rows with Request_IDs 17 and 19 – Radoslav.Ivanov Jan 22 '16 at 19:55
what are you changing @ToRoleID to? – JamieD77 Jan 22 '16 at 20:06
if you change @ToRoleID to 2 you would get 17, but not 19 because 19 has a RequestType = 13, and you're filtering by RequestType = 1 – JamieD77 Jan 22 '16 at 20:09

Using Partition and Join in MS SQL Server

1 Answers1