How to select a row having SubjectCode X if not then select SubjectCode as Y and so on as mentioned in code

Question

In this query I want to select rows from Question table where Subject Code is X if not then Go for Y and so on as mentioned in the case statement.

select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number, 
           SubjectCode,*
FROM       dbo.Question mcl 
inner join Answer mclo 
on         mclo.AnswerID = mcl.AnswereID
where      StudentID = 6588
and        mclo.SubjectCode = (case when mclo.SubjectCode ='X' then 'X' 
                               else 
                                 case when mclo.SubjectCode ='Y' then 'Y' 
                                 else 
                                   case when mclo.SubjectCode ='Z' then 'Z' 
                                   else 
                                     case when mclo.SubjectCode ='P' then 'P' 
                                     end  
                                   end 
                                 end 
                               end)

score 3 · Answer 1 · answered Feb 02 '18 at 11:37

as fas as I understand you would like to control the order of execution if x is not present search for y and so on. Therefore a simple IN clause or the case expressions probably won't work if you've got entries for x as well as for y and z....by result returning multiple rows.

The best way I can think of would be to introduce a priority factor within a cte and use this to return only the relevant row.

Here is an example. For simplicity I use a table with the following structure

CREATE TABLE test ( StudentID int, SubjectCode char(1) );

The table has the following entries:

As you described we'd like to return X first, then possibly Y (if no X present) and at last Z and only one row per student if the applicable subject code.

XYZ is easy because it is alphabetical but let's introduce some additional complexity guessing that there could be also an A which we would like to return if there is no X.

Here is a SQL Fiddle with my example

I would use the following query to achieve this:

    WITH 
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('A', 2), ('Y', 3), ('Z', 4)
        ) AS tab(code,prio)
  )
 SELECT StudentId, SubjectCode
 FROM
 (
 SELECT 
   StudentId,
   SubjectCode,
   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn
 FROM test t
 INNER JOIN cteSortLogic  sl ON t.SubjectCode = sl.code
 WHERE SubjectCode IN ('X', 'Y', 'Z')
 ) res
 WHERE res.rn = 1
;

The CTE gives me a simple table with the codes and their priority of output:

Then I join it to my table and add a rownumber based on the priority: So here's the output of my subselect (or derived query) named "res":

I have still got all the rows but assigned a rownumber based on my priority. Then the last thing I have to do is to filter only the most relevant rows which are the one having row number 1 .

Presto...here's the result of the whole query:

So given your example query your code would look like this:

    WITH 
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('Y', 2), ('Z', 3)
        ) AS tab(code,prio)
  )
SELECT *
FROM
(
select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number, 
           SubjectCode,   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn, *
FROM       dbo.Question mcl 
inner join Answer mclo on         mclo.AnswerID = mcl.AnswereID
inner join cteSortLogic sl on mclo.SubjectCode = sl.code
where      StudentID = 6588
AND         SubjectCode IN ('X', 'Y', 'Z')
)
WHERE
    rn = 1

McNets · Answer 2 · 2018-02-02T08:45:19.530

You can find a related question here:

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Having a look at CASE on MS-Docs about :

Return Values

Simple CASE expression:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

Allows only an equality check.

In the order specified, evaluates input_expression = when_expression for each WHEN clause.

Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Searched CASE expression:

Evaluates, in the order specified, Boolean_expression for each WHEN clause.

Returns result_expression of the first Boolean_expression that evaluates to TRUE.

If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

You could rewrite your query as:

SELECT     dbo.GetStudentNumber('M', mcl.StudentID) AS Student_Number, 
           SubjectCode,*
FROM       dbo.Question mcl 
INNER JOIN Answer mclo 
ON         mclo.AnswerID = mcl.AnswereID
WHERE      StudentID = 6588
AND        mclo.SubjectCode = CASE mclo.SubjectCode WHEN 'X' THEN 'X'
                                                    WHEN 'Y' THEN 'Y'
                                                    WHEN 'Z' THEN 'Z'
                                                    WHEN 'P' THEN 'P' END;

DECLARE @TEST INT;
SET @TEST = 1;
WHILE @TEST <= 5
BEGIN
SELECT CASE @TEST WHEN 1 THEN 100
                  WHEN 2 THEN 200
                  WHEN 3 THEN 300
                  WHEN 4 THEN 400
                  ELSE 500 END TEST;


SET @TEST += 1;

END;
GO

| TEST |
| ---: |
|  100 |

| TEST |
| ---: |
|  200 |

| TEST |
| ---: |
|  300 |

| TEST |
| ---: |
|  400 |

| TEST |
| ---: |
|  500 |

dbfiddle here

score 0 · Answer 3 · answered Feb 02 '18 at 11:37

You could do it something like this:

SELECT     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number, 
           SubjectCode,*
FROM       dbo.Question mcl 
inner join Answer mclo on mclo.AnswerID = mcl.AnswereID
INNER JOIN (SELECT TOP 1 subjectcode, studentid FROM Question ORDER BY CASE WHEN subjectcode = 'X' THEN 0
                WHEN subjectcode = 'Y' THEN 1
                WHEN subjectcode = 'Z' THEN 2
                WHEN subjectcode = 'P' THEN 3
                ELSE 10
                END) AS bestCode ON mcl.studentid = bestCode.studentid 
where      StudentID = 6588
and        mclo.SubjectCode = bestCode.SubjectCode

Where by the subquery bestCode finds the most significant subjectcode for each studentid.

How to select a row having SubjectCode X if not then select SubjectCode as Y and so on as mentioned in code

3 Answers3