(MS)SQL Query within a Query

July 23, 2009 1:09 AM

I have a basic sql query which returns all jobs run between specified dates as follows: SELECT job_id, starttime, size FROM job_history WHERE (starttime > FromDate) AND (startTime < ToDate) But I also want the query to return the previous size for that job_id

I've tried:
SELECT job_id, starttime, size,
(SELECT Top 1 size FROM job_history
WHERE starttime [less than] FromDate
AND job_id = job_id
ORDER BY starttime DESC) AS Previous_Size
FROM job_history
WHERE (starttime > FromDate) AND (startTime [less than] ToDate)

I'm guessing that I need a variable in ther or something but I'm a bit out of my depth.

([less than] used to indicate opposite of > as it seems to dissapear when posted.)

posted by xla76 to Computers & Internet (4 answers total)

Best answer: you need to alias the job_histories so that it's not ambiguous and it compares different job_ids - something like this:

SELECT a.job_id, a.starttime, a.size,
(SELECT Top 1 b.size FROM job_history AS b
WHERE b.starttime [less than] b.FromDate
AND a.job_id = b.job_id
ORDER BY b.starttime DESC) AS Previous_Size
FROM job_history AS a
WHERE (a.starttime > a.FromDate) AND (a.startTime [less than] a.ToDate)
posted by radicarian at 1:33 AM on July 23, 2009

Best answer: 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.
posted by orthogonality at 1:37 AM on July 23, 2009

Response by poster: Thanks all - works perfectly.
orthogonality, what would you use in this situation rather than TOP?
posted by xla76 at 4:32 AM on July 23, 2009

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);
posted by orthogonality at 7:47 PM on July 23, 2009