Sql-server – limit the number of rows returned when a condition is met

sql serversql-server-2008-r2t-sql

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

Best Answer

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

Related Solutions

Sql-server – Use a condition to limit the databases a policy checks

You can certainly limit the set of databases that are checked, using a condition, and have that condition be separate from the actual condition the policy is using to check. Pull down the "Every" drop-down in the UI:

enter image description here

Create a new condition:

enter image description here

Then you should be able to use that condition:

enter image description here

Note that this is using the SQL Server 2012 version of Management Studio (which is now completely free, as of SP1). If the UI does not behave this way in the 2008 R2 version of the tools, upgrade your tools.

SQL Server – Selecting Rows with Group Condition Without Temporary Table

GROUP BY cannot be used alone because it only returns 1 row per group (category).

You can use a sub query with flag = 1 and INNER JOIN:

SELECT d1.ID, d1.category, d1.flag
FROM data d1
INNER JOIN (
    SELECT DISTINCT category FROM data WHERE flag = 1
) d2 
    ON d2.category = d1.category ;

You can use the EXISTS clause:

SELECT d.ID, d.category, d.flag
FROM data d
WHERE EXISTS (
    SELECT 1 FROM data WHERE flag = 1 AND category = d.category
) ;

You can use IN clause (although EXISTS is better):

SELECT d.ID, d.category, d.flag
FROM data d
WHERE d.category IN (SELECT category FROM data WHERE flag = 1) ;

You can also use CROSS APPLY with a sub query on flag = 1:

SELECT d.ID, d.category, d.flag
FROM data d
CROSS APPLY (
    SELECT TOP (1) category 
    FROM data 
    WHERE flag = 1 AND category = d.category
) ca ;

DISTINCT aren't needed if, for each category, only 1 row can have flag = 1.

Output:

ID  category    flag
1       A       1
2       A       0
3       A       0

Best Answer

Related Solutions

Sql-server – Use a condition to limit the databases a policy checks

SQL Server – Selecting Rows with Group Condition Without Temporary Table

Output:

Related Question