Mysql – Using a subquery in a LIKE statement

Yes, most analytic functions can be rewritten with semi-join. In your case it would probably not be very efficient:

SELECT <select_list>
  FROM <table_list>
 WHERE <where_conditions>
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (ML.MASTERMERCHANTID, WMP.PRODUCTCODE) 
                  = (ml_in.MASTERMERCHANTID,wmp_in.PRODUCTCODE)
          )
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (WMP.MERCHANTID, WMP.PRODUCTCODE) 
                  = (wmp_in.MERCHANTID, wmp_in.PRODUCTCODE)
          )

Looking at your table structures, I would suggest that you change the design to something that is normalized. For example:

create table tbl_subjects
(
  sub_id int,
  subject_name varchar(25)
);

create table tbl_cmarks
(
  c_id int,
  examid int,
  rollno int,
  sub_id int,
  mark int
);

Using something similar to above will allow you to add new subjects without having to alter your table. Then you will just join the tables on the sub_id to get the list of subjects for each class.

select ta.rollno,ta.StdNm,tc.C_Name,
  tm.mark,
  s.subject
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22 
  and tm.Examid=9;

See SQL Fiddle with Demo. The above will give you a result in rows, but you could easily apply an aggregate function with a CASE expression to pivot the data into columns. Similar to the following:

select ta.rollno,
  ta.StdNm,
  tc.C_Name,
  max(case when s.subject = 'English' then tm.mark end) Emglish,
  max(case when s.subject = 'Physics' then tm.mark end) Physics
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22 
  and tm.Examid=9
group by  ta.rollno, ta.StdNm, tc.C_Name;

See Demo.

But if you don't change your current table structure and if you don't know the columns that you are going to return, then you will have to implement a prepared statement to generate dynamic SQL.

First, you will create the list of the classes:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject)) 
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

See Demo. This will give you the list of classes for each c_id. Once you have the list of classes, then you can add this to the rest of the sql string so the full code will be:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject)) 
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

SET @query 
  = CONCAT('SELECT ta.rollno,ta.StdNm,tc.C_Name, ', @sqlList, ' 
            FROM tbl_cmarks tm
            INNER JOIN tbl_classes tc
              ON tm.Cid = tc.C_Id
            INNER JOIN tbl_admission ta
              ON ta.rollno = tm.rollno
            where tm.Cid = 22 
              and tm.Examid=9');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo