Using Multiple Columns in a SQL Subquery

Question

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!

You shouldn't need to include all the columns in your WHERE in your SELECT... what's the error that you're getting? — Michael Fredrickson
– Michael Fredrickson, Commented Feb 20, 2013 at 18:21

sgeddes · Accepted Answer · 2013-02-20 18:23:58Z

3

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.

edited Feb 20, 2013 at 18:23

answered Feb 20, 2013 at 18:17

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

The correct expression is row_number() over (partition by parent_study_id order by activity_date).

sgeddes Over a year ago

@GordonLinoff -- thanks, didn't see that in original query -- working on Fiddle and went too fast! Thanks!

Oleksandr Fedorenko · Accepted Answer · 2013-02-20 19:07:41Z

0

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

edited Feb 20, 2013 at 19:07

answered Feb 20, 2013 at 18:55

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Collectives™ on Stack Overflow

Using Multiple Columns in a SQL Subquery

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related