SQL query inlvolving  "atleast"

Posted on 2007-07-24
Medium Priority
Last Modified: 2013-12-07
     I have a question regarding SQL Query. For ex:- consider the following schema.

Student table:

studentID      name     sub1     sub2     sub3     sub4      sub5  
1001             abc         67         34          56         78         89      
1002              xyz         67         65          23         76          45

sub1,sub2,sub3... fields contain the marks in that particular subjects.

Query :-  Find the names of the students who got 50 in atleast 3 subjects.

to make this query generic, let
m   -   be the number of subjects(for the above query it is "5")
n    -   is the atleast count(for the above query it is "3")
           I am having a touch time to write a query which is generic i.e m , n can be any value.Any help would be greatly appreciated.

Database : SQL Server 2000
                     It would be great if the query is not specific to any Database vendor i.e if it is according to standards.


Question by:ramesh_axl
  • 2
LVL 93

Accepted Solution

Patrick Matthews earned 375 total points
ID: 19559419
SELECT studentID
FROM Student
WHERE ((CASE WHEN sub1 > 49 THEN 1 ELSE 0 END) +
    (CASE WHEN sub2 > 49 THEN 1 ELSE 0 END) +
    (CASE WHEN sub3 > 49 THEN 1 ELSE 0 END) +
    (CASE WHEN sub4 > 49 THEN 1 ELSE 0 END) +
    (CASE WHEN sub5 > 49 THEN 1 ELSE 0 END)) > 2

Author Comment

ID: 19559505
Hi Patrik,

          Thanks for your  reply. I am trying to write a query which is generic i.e i will be knowing the number of subjects at run time.

Author Comment

ID: 19560096
Hi Patrick,
           I am able to work with the solution you have given.

Thanks a lot

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question