(MS)SQL Query within a Query

You need a correlation name in your subquery, without it, it's comparing every job_id in the subquery table to itself, and all (that are not null) are equal to themselves, so you get back every row. By using the correlation name, you make the predicate insist that the rows in the subquery equal the job_id of the row in the main query: SELECT job_id, starttime, size, (SELECT Top 1 B.size FROM job_history B WHERE B.starttime < FromDate AND B.job_id = A.job_id ORDER BY B.starttime DESC) AS Previous_Size FROM job_history A WHERE (starttime > FromDate) AND (startTime < ToDate); Also, your range should include one "comparison or equal" operator, else you'll never find rows where starttime exactly equals FromDate/ToDate: SELECT job_id, starttime, size, (SELECT Top 1 B.size FROM job_history B WHERE B.starttime < FromDate AND B.job_id = A.job_id ORDER BY B.starttime DESC) AS Previous_Size FROM job_history A WHERE (starttime >= FromDate) AND (startTime < ToDate); Also, I don't like using top or limit, but, eh.

Thanks all - works perfectly. orthogonality, what would you use in this situation rather than TOP?

orthogonality, what would you use in this situation rather than TOP? Very briefly, as I'm busy and ill: I'd prefer to do it by getting the set I needed, rather than relying on a sort and grabbing the TOP 1. SELECT job_id, starttime, size, (SELECT B.size FROM job_history B WHERE B.job_id = A.job_id and B.starttime = (select max( c.starttime) from job_history c where c.job_id = a.job_id and c.starttime < FromDate ) ) AS Previous_Size FROM job_history A WHERE (starttime >= FromDate) AND (startTime < ToDate); Now, we might have more than one row with the same starttime and job_id, so the max(starttime) less then FromDate might match more than one row in B, so: SELECT job_id, starttime, size, (SELECT avg(B.size) FROM job_history B WHERE B.job_id = A.job_id and B.starttime = (select max( c.starttime) from job_history c where c.job_id = a.job_id and c.starttime < FromDate ) ) AS Previous_Size FROM job_history A WHERE (starttime >= FromDate) AND (startTime < ToDate);