How to select a row having SubjectCode X if not then select SubjectCode as Y and so on as mentioned in code 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)
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