limit the number of rows returned when a condition is met? Is it possible to limit the number of rows returned when a condition is met?
I am working on a query to check if a student is ready to graduate, and they need to meet a certain number of credits per subject. I don't want all classes, because any class past the number of credits needed can be used for electives.

EDIT: I forgot to mention that this is SQL 2008 R2

I was hoping to be able to do something like this (which I know doesn't work)

select top(sum(credits) &gt; 3.0) c.name, c.subject, c.credits, c.pass
from classes as c
where c.Subject = 'Math' and c.passed = 1


Any help would be great



Data

Subject        |  Name             | Grade | Credit | Pass
Social Studies | DL Psychology     | 83    | 0.50   | 1
Social Studies | Global Studies 10 | 82    | 1.00   | 1
Social Studies | Global Studies 9  | 83    | 1.00   | 1
Social Studies | Part. In Govern   | 84    | 0.50   | 1
Social Studies | US History 11     | 87    | 1.00   | 1


Query

select c.Subject, 
           c.Name, 
           c.credits, 
           c.pass, 
           c.score
    from @classes as c 
    where (c.Subject = 'Social Studies' and c.pass = 1 and c.Name like '%Econ%')
    or    (c.Subject = 'Social Studies' and c.pass = 1 and c.Name like '%Gov%')
    or    (c.Subject = 'Social Studies' and c.pass = 1)
    group by c.Subject, c.Name, c.credits, c.pass, c.score
    having Sum(credits) &lt;= 2.0


Im exprecting to see these rows returned

Expected Results

Subject        | Name              | Grade | Credit | Pass
Social Studies | Part. In Govern   | 84    | 0.50   | 1
Social Studies | DL Psychology     | 83    | 0.50   | 1
Social Studies | Global Studies 10 | 82    | 1.00   | 1
I'm not sure how this will scale but it's an answer :)

I should note that I'm summing for CN.RowNum &lt; ClassNumbered.RowNum and not &lt;= for a reason.  This way I can return everything lower than the requested value (2.0 in this case).  If I did &lt;= then I would have to return everything &lt;= 2.0 which won't work if the three values happen to be .5, 1 and 1 for a total of 2.5.  You wouldn't get the last class.  I also broke this up by subject so that if you have more than one subject you will get multiple groupings by subject.

WITH ClassNumbered AS (SELECT [Subject], Name, Grade, Credit, Pass,
                        ROW_NUMBER() OVER (ORDER BY [Subject],Credit,Grade) AS RowNum
                    FROM Classes),
    ClassTotals AS (SELECT [Subject], Name, Grade, Credit, Pass,
                        (SELECT ISNULL(SUM(Credit),0) FROM ClassNumbered CN
                            WHERE CN.[Subject] = ClassNumbered.[Subject]
                              AND CN.RowNum &lt; ClassNumbered.RowNum) AS RunningTotal
                    FROM ClassNumbered)
SELECT * 
FROM ClassTotals
WHERE RunningTotal &lt; 2.0