CODEWITHSUNDEEP
sqlsql-server
Luke Belbina
Luke Belbina

Reputation: 5859

Using Multiple Columns in a SQL Subquery

My setup is I have two tables, Study and Activity_History. Activities run on studies so there is a 1:many relationship.

I want to be able to run a SQL query on an Activity_History table which will get me the activity and the previously run activity. I currently have this:

SELECT 
        *
FROM Activity_History AS A1
LEFT JOIN Activity_History AS A2
ON A2.Parent_Study_ID =
(
    SELECT TOP 1 Parent_Study_ID
    FROM Activity_History AS A3
    WHERE A3.Parent_Study_ID = A1.Parent_Study_ID 
          AND A3.Activity_Date < A1.Activity_Date
    ORDER BY Activity_Date DESC
)

This is not working. What's happening is its pulling the Activity_Date party of the query has no effect and it just returns the first matching Activity_Date in descending date order for every row. I think this is happening because in my subquery I am using Activity_Date in the where, but this is not in the subquery select.

Thanks for any help!

Upvotes: 2

Views: 128

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

In SQLServer2005+ instead LEFT JOIN you need to use OUTER APPLY

SELECT *
FROM Activity_History AS A1 OUTER APPLY ( 
                                         SELECT TOP 1 Parent_Study_ID
                                         FROM Activity_History AS A2
                                         WHERE A2.Parent_Study_ID = A1.Parent_Study_ID 
                                           AND A2.Activity_Date < A1.Activity_Date
                                         ORDER BY A2.Activity_Date DESC
                                         ) o

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

I'm assuming you're using SQL Server? If so, then this should work using ROW_NUMBER():

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Parent_Study_Id ORDER BY Activity_Date ) RN
    FROM Activity_History
    )
SELECT * 
FROM CTE T1
    LEFT JOIN CTE T2 ON T1.RN = T2.RN+1 AND T1.Parent_Study_Id  = T2.Parent_Study_Id

And here is the SQL Fiddle.

Upvotes: 3

Related Questions