Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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) > 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) <= 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
share|improve this question
    
Looks like you're trying to do something like a running total. The primary issue with what you're trying to do is that in order to have a TOP or a running total, you need to have a field you're ordering by. What are you ordering by to identify the classes a student has taken to meet your graduation requirements? –  Mike Fal Apr 26 '13 at 16:10
    
All of the classes inside of @classes are ones this student has taken and completed. I don't think it really matters how I order them. If I had to pick I guess I might order by the score. The conditions I wrote in the query should get all of the classes with a subject area of Social studies, but get the ones in the where and the first or statement first, and then any other class with the correct subject area... as long as the total of the first 2 classes credits + the next classes credits don't exceed the limit in the having clause. –  Dan Whitehouse Apr 26 '13 at 16:20
    
You want a recursive CTE for this. –  JNK Apr 26 '13 at 18:24

2 Answers 2

I'm not sure how this will scale but it's an answer :)

I should note that I'm summing for CN.RowNum < ClassNumbered.RowNum and not <= for a reason. This way I can return everything lower than the requested value (2.0 in this case). If I did <= then I would have to return everything <= 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 < ClassNumbered.RowNum) AS RunningTotal
                    FROM ClassNumbered)
SELECT * 
FROM ClassTotals
WHERE RunningTotal < 2.0
share|improve this answer

This is definitely not the best of ideas, but you could always use a table, sorry if this is incorrect syntax (more trying to get the idea across) of a terrible idea in general as I'm not the most adept at SQL, just trying to help spitball ideas for you!

CREATE TABLE @ClassTable
DECLARE @Counter int = 0;

While (@Counter < 3)
{
    SELECT INTO @ClassTable TOP 1 
    FROM [TBL]
    WHERE [Condition] AND
    NOT IN (SELECT [Class]
            FROM [TBL])
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.