How to add a where clause to a column from a correlated subquery

Question

I have the following correlated subquery in SQL Server which works fine

select *, [Status]=(select Max([Status]) from Data_121EmailLog o2 
where o2.Data_121Id = o1.Data_121Id) from Data_121 o1

You can see what's going on here from this screenshot

enter image description here

However, when I try to add a where clause on the column in the generated subquery it doesn't work

enter image description here

How can I added a where clause on the [Status] column. In my example this should return 1 result as there is only 1 record with a status of 2.

You can't use column alias in `where` clause: http://stackoverflow.com/a/6591216/1163867 — MarcinJuraszek, Aug 24 '13 at 02:48

score 0 · Accepted Answer · answered Aug 24 '13 at 03:06

0

Following the comment link by MarcinJuraszek solved it for me

SELECT * FROM (
select *, [Status]=(select Max([Status]) from Data_121EmailLog o2 
where o2.Data_121Id = o1.Data_121Id) from Data_121 o1) a where a.[Status] = 2

answered Aug 24 '13 at 03:06

Petras

4,686
14
57
89

How to add a where clause to a column from a correlated subquery

1 Answers1