SELECT * FROM ( (SELECT NULL AS pos2, NULL AS class_spec, NULL AS class_order, NULL AS op2, NULL AS pro2, NULL AS am2, NULL AS wo2, NULL AS leg2, NULL AS wleg2, NULL AS 17_2, NULL AS w17_2, CASE WHEN posPro IS NOT NULL THEN posPro WHEN posAm IS NOT NULL THEN posAm WHEN posWo IS NOT NULL THEN posWo WHEN posMas IS NOT NULL THEN posMas WHEN posWMas IS NOT NULL THEN posWMas WHEN posJun IS NOT NULL THEN posJun WHEN posWJun IS NOT NULL THEN posWJun END as Pos, CASE WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_eps_spec WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_eps_spec WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_eps_spec WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_eps_spec WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_eps_spec WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_eps_spec WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_eps_spec WHEN base_class = 'NA' THEN 'NA' ELSE 'Registered!' END AS eps_spec, CASE WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_public_eps_spec WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_public_eps_spec WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_public_eps_spec WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_public_eps_spec WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_public_eps_spec WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_public_eps_spec WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_public_eps_spec WHEN base_class = 'NA' THEN 'NA' ELSE 'Registered!' END AS public_eps_spec, CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') AND (e_class=1 OR e_class=3) THEN 'OP' ELSE CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') THEN racer_reg_class ELSE CASE WHEN LOCATE(CONCAT(',',racer_reg_class,','),event_class_plan)>0 THEN racer_reg_class ELSE CASE WHEN racer_reg_class='WGL' AND LOCATE(',WLE,',event_class_plan)>0 THEN 'WLE' ELSE CASE WHEN racer_reg_class='GL' AND LOCATE(',LEG,',event_class_plan)>0 THEN 'LEG' ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',11,',event_class_plan)>0 THEN '11' ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',W14,',event_class_plan)>0 THEN 'W14' ELSE CASE WHEN (racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',14,',event_class_plan)>0 THEN '14' ELSE CASE WHEN (racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',W17,',event_class_plan)>0 THEN 'W17' ELSE CASE WHEN (racer_reg_class='14' OR racer_reg_class='W14' OR racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',17,',event_class_plan)>0 THEN '17' ELSE CASE WHEN (racer_reg_class='WGL' OR racer_reg_class='WLE' OR racer_reg_class='W17' OR racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',WO,',event_class_plan)>0 THEN 'WO' ELSE CASE WHEN e_class=1 OR e_class=3 THEN 'OP' ELSE UPPER(class) END END END END END END END END END END END END AS event_race_class, RESULT.* FROM ( SELECT CASE WHEN RL.base_class = 'PRO' THEN @curRowPro := @curRowPro+1 ELSE NULL END as posPro, CASE WHEN RL.base_class = 'AM' THEN @curRowAm := @curRowAm+1 ELSE NULL END as posAm, CASE WHEN RL.base_class = 'WO' THEN @curRowWo := @curRowWo+1 ELSE NULL END as posWo, CASE WHEN RL.base_class = 'LEG' THEN @curRowMas := @curRowMas+1 ELSE NULL END as posMas, CASE WHEN RL.base_class = 'WLE' THEN @curRowWMas := @curRowWMas+1 ELSE NULL END as posWMas, CASE WHEN RL.base_class = '17' THEN @curRowJun := @curRowJun+1 ELSE NULL END as posJun, CASE WHEN RL.base_class = 'W17' THEN @curRowWJun := @curRowWJun+1 ELSE NULL END as posWJun, RL.*, CASE WHEN rank_order = 10000 THEN CONCAT('No ',rank_string_type,' rank ',rank_string_year) ELSE CONCAT(rank_order,' ranked ',rank_string_type,' ',SUBSTRING(base_rank_class,4),' ',rank_string_year) END AS rank_place FROM ( SELECT R.*, CASE WHEN R.reg_class IS NOT NULL AND R.reg_class>'' THEN R.reg_class ELSE CASE WHEN R.default_race_class IS NOT NULL AND R.default_race_class>'' THEN R.default_race_class ELSE R.base_class END END AS racer_reg_class, CASE WHEN R.base_class = 'PRO' THEN rnk_year WHEN R.base_class = 'AM' THEN rnk_year WHEN R.base_class = 'WO' THEN rnk_year WHEN R.base_class = 'LEG' THEN rnk_year WHEN R.base_class = 'WLE' THEN rnk_year WHEN R.base_class = '17' THEN rnk_year WHEN R.base_class = 'W17' THEN rnk_year END AS rank_string_year, CASE WHEN R.base_class = 'PRO' THEN 'Season' WHEN R.base_class = 'AM' THEN 'Season' WHEN R.base_class = 'WO' THEN 'Season' WHEN R.base_class = 'LEG' THEN 'Season' WHEN R.base_class = 'WLE' THEN 'Season' WHEN R.base_class = '17' THEN 'Season' WHEN R.base_class = 'W17' THEN 'Season' END AS rank_string_type, CASE WHEN R.base_class = 'PRO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'AM' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'LEG' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WLE' THEN COALESCE(Y.place,10000) WHEN R.base_class = '17' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'W17' THEN COALESCE(Y.place,10000) END AS rank_order FROM ( SELECT CAST(CASE WHEN P.class = 'PRO' THEN CONCAT('1. ',P.class ) WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' THEN '7. JUNIOR (Girl)' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) THEN '4. JUNIOR' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' THEN '6. MASTER (Woman)' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) THEN '2. MASTER' WHEN P.sex = 'F' THEN '5. WOMAN' WHEN P.class = 'AM' THEN '3. AM' ELSE '9. NOT VALID REGISTRATION' END AS CHAR(20)) AS base_rank_class, CASE WHEN P.class = 'PRO' THEN 'PRO' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' THEN 'W17' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) THEN '17' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' THEN 'WLE' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) THEN 'LEG' WHEN P.sex = 'F' THEN 'WO' WHEN P.class = 'AM' THEN 'AM' ELSE 'NA' END AS base_class, CONCAT('[', C.country, CASE WHEN P.State > '' THEN CONCAT(' - ',S.State) ELSE '' END, '] ',P.First_name,' ',P.Family_name) AS Racer, CASE WHEN P.Birth_year <1900 THEN '-' ELSE CAST( YEAR( CURRENT_DATE ) - birth_year AS CHAR ) END AS Age, EP.base_disc_order, EP.comp_no, EP.pid, P.family_name, P.first_name, P.nick_name, P.nationality, C.cc, C.country, D.flag_pid, EP.event_pers_stat, EP.reg_class, P.class, P.default_race_class, E.e_class, E.add_class_plan, CONCAT(',',E.add_class_plan,',') AS event_class_plan, PS.eps_spec AS org_eps_spec, PS.public_eps_spec AS org_public_eps_spec, date_reg_end, CASE WHEN YEAR(date_start) > RS.value_integer THEN RS.value_integer ELSE YEAR(date_start) END AS rnk_year, CASE WHEN YEAR(date_start) > RSL.value_integer THEN RSL.value_integer ELSE YEAR(date_start) END AS lim_year FROM event_pers EP JOIN person P ON P.pid = EP.pid AND P.first_name <> 'T-e-s-t' JOIN event E ON E.eid = EP.eid JOIN rank_settings RS on RS.RSID=0 JOIN rank_settings RSL on RSL.RSID=10 JOIN lu_event_pers_stat PS ON PS.lu_eps_id = EP.event_pers_stat JOIN country C ON C.cid = P.Nationality LEFT JOIN states S ON S.stid = P.state LEFT JOIN ( SELECT DISTINCT pid AS flag_pid FROM ( SELECT pid, max(group_year) as year FROM (SELECT pid, gid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) PT JOIN `group` G ON G.gid=PT.gid AND G.group_year>=YEAR(CURDATE())-1 GROUP BY pid UNION SELECT pid, MAX(year) AS latest_top_rank_year FROM rank_result_year WHERE year>YEAR(CURDATE())-10 AND ((class = 'PRO' AND place <=10) OR (class = 'WO' AND place <=5)) GROUP BY pid UNION SELECT * FROM ( SELECT pid, count(ptid) as title_count FROM (SELECT pid, ptid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) A GROUP BY pid ) A WHERE A.title_count>=10 ) B ) D ON D.flag_pid=P.pid WHERE EP.eid ='1200' ) R LEFT JOIN rank_result_year Y on Y.year=rnk_year and Y.class=R.base_class and Y.pid=R.pid LEFT JOIN rank_lim RL on RL.year=lim_year and RL.rank_class=R.base_class and RL.pid=R.pid ) AS RL JOIN (SELECT @curRowPro := 0, @curRowAm := 0, @curRowWo := 0, @curRowMas := 0, @curRowWMas := 0, @curRowJun := 0, @curRowWJun := 0 ) q ORDER BY RL.base_rank_class, RL.base_disc_order, RL.rank_order ) AS RESULT) UNION (SELECT CASE WHEN posOp2 IS NOT NULL THEN posOp2 WHEN posPro2 IS NOT NULL THEN posPro2 WHEN posAm2 IS NOT NULL THEN posAm2 WHEN posWo2 IS NOT NULL THEN posWo2 WHEN posMas2 IS NOT NULL THEN posMas2 WHEN posWMas2 IS NOT NULL THEN posWMas2 WHEN posJun2 IS NOT NULL THEN posJun2 WHEN posWJun2 IS NOT NULL THEN posWJun2 END as Pos2, RESULT3.* FROM ( SELECT dc.class_spec, dc.class_order, CASE WHEN event_race_class = 'OP' THEN @curRowOp2 := @curRowOp2+1 ELSE NULL END as posOp2, CASE WHEN event_race_class = 'PRO' THEN @curRowPro2 := @curRowPro2+1 ELSE NULL END as posPro2, CASE WHEN event_race_class = 'AM' THEN @curRowAm2 := @curRowAm2+1 ELSE NULL END as posAm2, CASE WHEN event_race_class = 'WO' THEN @curRowWo2 := @curRowWo2+1 ELSE NULL END as posWo2, CASE WHEN event_race_class = 'LEG' THEN @curRowMas2 := @curRowMas2+1 ELSE NULL END as posMas2, CASE WHEN event_race_class = 'WLE' THEN @curRowWMas2 := @curRowWMas2+1 ELSE NULL END as posWMas2, CASE WHEN event_race_class = '17' THEN @curRowJun2 := @curRowJun2+1 ELSE NULL END as posJun2, CASE WHEN event_race_class = 'W17' THEN @curRowWJun2 := @curRowWJun2+1 ELSE NULL END as posWJun2, RESULT2.* FROM ( SELECT CASE WHEN posPro IS NOT NULL THEN posPro WHEN posAm IS NOT NULL THEN posAm WHEN posWo IS NOT NULL THEN posWo WHEN posMas IS NOT NULL THEN posMas WHEN posWMas IS NOT NULL THEN posWMas WHEN posJun IS NOT NULL THEN posJun WHEN posWJun IS NOT NULL THEN posWJun END as Pos, CASE WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_eps_spec WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_eps_spec WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_eps_spec WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_eps_spec WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_eps_spec WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_eps_spec WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_eps_spec WHEN base_class = 'NA' THEN 'NA' ELSE 'Registered!' END AS eps_spec, CASE WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_public_eps_spec WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_public_eps_spec WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_public_eps_spec WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_public_eps_spec WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_public_eps_spec WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_public_eps_spec WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_public_eps_spec WHEN base_class = 'NA' THEN 'NA' ELSE 'Registered!' END AS public_eps_spec, CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') AND (e_class=1 OR e_class=3) THEN 'OP' ELSE CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') THEN racer_reg_class ELSE CASE WHEN LOCATE(CONCAT(',',racer_reg_class,','),event_class_plan)>0 THEN racer_reg_class ELSE CASE WHEN racer_reg_class='WGL' AND LOCATE(',WLE,',event_class_plan)>0 THEN 'WLE' ELSE CASE WHEN racer_reg_class='GL' AND LOCATE(',LEG,',event_class_plan)>0 THEN 'LEG' ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',11,',event_class_plan)>0 THEN '11' ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',W14,',event_class_plan)>0 THEN 'W14' ELSE CASE WHEN (racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',14,',event_class_plan)>0 THEN '14' ELSE CASE WHEN (racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',W17,',event_class_plan)>0 THEN 'W17' ELSE CASE WHEN (racer_reg_class='14' OR racer_reg_class='W14' OR racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',17,',event_class_plan)>0 THEN '17' ELSE CASE WHEN (racer_reg_class='WGL' OR racer_reg_class='WLE' OR racer_reg_class='W17' OR racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',WO,',event_class_plan)>0 THEN 'WO' ELSE CASE WHEN e_class=1 OR e_class=3 THEN 'OP' ELSE UPPER(class) END END END END END END END END END END END END AS event_race_class, RESULT.* FROM ( SELECT CASE WHEN RL.base_class = 'PRO' THEN @curRowPro := @curRowPro+1 ELSE NULL END as posPro, CASE WHEN RL.base_class = 'AM' THEN @curRowAm := @curRowAm+1 ELSE NULL END as posAm, CASE WHEN RL.base_class = 'WO' THEN @curRowWo := @curRowWo+1 ELSE NULL END as posWo, CASE WHEN RL.base_class = 'LEG' THEN @curRowMas := @curRowMas+1 ELSE NULL END as posMas, CASE WHEN RL.base_class = 'WLE' THEN @curRowWMas := @curRowWMas+1 ELSE NULL END as posWMas, CASE WHEN RL.base_class = '17' THEN @curRowJun := @curRowJun+1 ELSE NULL END as posJun, CASE WHEN RL.base_class = 'W17' THEN @curRowWJun := @curRowWJun+1 ELSE NULL END as posWJun, RL.*, CASE WHEN rank_order = 10000 THEN CONCAT('No ',rank_string_type,' rank ',rank_string_year) ELSE CONCAT(rank_order,' ranked ',rank_string_type,' ',SUBSTRING(base_rank_class,4),' ',rank_string_year) END AS rank_place FROM ( SELECT R.*, CASE WHEN R.reg_class IS NOT NULL AND R.reg_class>'' THEN R.reg_class ELSE CASE WHEN R.default_race_class IS NOT NULL AND R.default_race_class>'' THEN R.default_race_class ELSE R.base_class END END AS racer_reg_class, CASE WHEN R.base_class = 'PRO' THEN rnk_year WHEN R.base_class = 'AM' THEN rnk_year WHEN R.base_class = 'WO' THEN rnk_year WHEN R.base_class = 'LEG' THEN rnk_year WHEN R.base_class = 'WLE' THEN rnk_year WHEN R.base_class = '17' THEN rnk_year WHEN R.base_class = 'W17' THEN rnk_year END AS rank_string_year, CASE WHEN R.base_class = 'PRO' THEN 'Season' WHEN R.base_class = 'AM' THEN 'Season' WHEN R.base_class = 'WO' THEN 'Season' WHEN R.base_class = 'LEG' THEN 'Season' WHEN R.base_class = 'WLE' THEN 'Season' WHEN R.base_class = '17' THEN 'Season' WHEN R.base_class = 'W17' THEN 'Season' END AS rank_string_type, CASE WHEN R.base_class = 'PRO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'AM' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'LEG' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WLE' THEN COALESCE(Y.place,10000) WHEN R.base_class = '17' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'W17' THEN COALESCE(Y.place,10000) END AS rank_order FROM ( SELECT CAST(CASE WHEN P.class = 'PRO' THEN CONCAT('1. ',P.class ) WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' THEN '7. JUNIOR (Girl)' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) THEN '4. JUNIOR' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' THEN '6. MASTER (Woman)' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) THEN '3. MASTER' WHEN P.sex = 'F' THEN '5. WOMAN' WHEN P.class = 'AM' THEN '2. AM' ELSE '9. NOT VALID REGISTRATION' END AS CHAR(20)) AS base_rank_class, CASE WHEN P.class = 'PRO' THEN 'PRO' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' THEN 'W17' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) THEN '17' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' THEN 'WLE' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) THEN 'LEG' WHEN P.sex = 'F' THEN 'WO' WHEN P.class = 'AM' THEN 'AM' ELSE 'NA' END AS base_class, CONCAT('[', C.country, CASE WHEN P.State > '' THEN CONCAT(' - ',S.State) ELSE '' END, '] ',P.First_name,' ',P.Family_name) AS Racer, CASE WHEN P.Birth_year <1900 THEN '-' ELSE CAST( YEAR( CURRENT_DATE ) - birth_year AS CHAR ) END AS Age, EP.base_disc_order, EP.comp_no, EP.pid, P.family_name, P.first_name, P.nick_name, P.nationality, C.cc, C.country, D.flag_pid, EP.event_pers_stat, EP.reg_class, P.class, P.default_race_class, E.e_class, E.add_class_plan, CONCAT(',',E.add_class_plan,',') AS event_class_plan, PS.eps_spec AS org_eps_spec, PS.public_eps_spec AS org_public_eps_spec, date_reg_end, CASE WHEN YEAR(date_start) > RS.value_integer THEN RS.value_integer ELSE YEAR(date_start) END AS rnk_year, CASE WHEN YEAR(date_start) > RSL.value_integer THEN RSL.value_integer ELSE YEAR(date_start) END AS lim_year FROM event_pers EP JOIN person P ON P.pid = EP.pid AND P.first_name <> 'T-e-s-t' JOIN event E ON E.eid = EP.eid JOIN rank_settings RS on RS.RSID=0 JOIN rank_settings RSL on RSL.RSID=10 JOIN lu_event_pers_stat PS ON PS.lu_eps_id = EP.event_pers_stat JOIN country C ON C.cid = P.Nationality LEFT JOIN states S ON S.stid = P.state LEFT JOIN ( SELECT DISTINCT pid AS flag_pid FROM ( SELECT pid, max(group_year) as year FROM (SELECT pid, gid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) PT JOIN `group` G ON G.gid=PT.gid AND G.group_year>=YEAR(CURDATE())-1 GROUP BY pid UNION SELECT pid, MAX(year) AS latest_top_rank_year FROM rank_result_year WHERE year>YEAR(CURDATE())-10 AND ((class = 'PRO' AND place <=10) OR (class = 'WO' AND place <=5)) GROUP BY pid UNION SELECT * FROM ( SELECT pid, count(ptid) as title_count FROM (SELECT pid, ptid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) A GROUP BY pid ) A WHERE A.title_count>=10 ) B ) D ON D.flag_pid=P.pid WHERE EP.eid ='1200' ) R LEFT JOIN rank_result_year Y on Y.year=rnk_year and Y.class=R.base_class and Y.pid=R.pid LEFT JOIN rank_lim RL on RL.year=lim_year and RL.rank_class=R.base_class and RL.pid=R.pid ) AS RL JOIN (SELECT @curRowPro := 0, @curRowAm := 0, @curRowWo := 0, @curRowMas := 0, @curRowWMas := 0, @curRowJun := 0, @curRowWJun := 0 ) q ORDER BY RL.base_rank_class, RL.base_disc_order, RL.rank_order ) AS RESULT ) AS RESULT2 JOIN discipline_class dc ON dc.class_type=event_race_class JOIN (SELECT @curRowOp2 := 0, @curRowPro2 := 0, @curRowAm2 := 0, @curRowWo2 := 0, @curRowMas2 := 0, @curRowWMas2 := 0, @curRowJun2 := 0, @curRowWJun2 := 0 ) q ORDER BY class_order, base_rank_class, base_disc_order, rank_order ) AS RESULT3) ) AS TOT ORDER BY class_order, Pos2, base_rank_class, Pos