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 TRUE THEN 0 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, CAST(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 UNSIGNED) AS rank_order, CAST(NULL AS UNSIGNED) AS rank_order2 FROM ( SELECT CAST(CASE WHEN P.class = 'PRO' THEN '1. PRO' ELSE '9. NOT VALID REGISTRATION' END AS CHAR(20)) AS base_rank_class, CASE WHEN P.class = 'PRO' THEN 'PRO' 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, PS.icon, 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 ='1047' ) 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 pos AS pos2, RESULT3.* FROM ( SELECT SUBSTR(RESULT2.base_rank_class,4,99) AS class_spec, LEFT(RESULT2.base_rank_class,1) 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, RESULT2.* FROM ( SELECT CASE WHEN TRUE THEN 0 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 @curRowPro3 := @curRowPro3+1 ELSE NULL END as posPro, CASE WHEN RL.base_class = 'AM' THEN @curRowAm3 := @curRowAm3+1 ELSE NULL END as posAm, CASE WHEN RL.base_class = 'WO' THEN @curRowWo3 := @curRowWo3+1 ELSE NULL END as posWo, CASE WHEN RL.base_class = 'LEG' THEN @curRowMas3 := @curRowMas3+1 ELSE NULL END as posMas, CASE WHEN RL.base_class = 'WLE' THEN @curRowWMas3 := @curRowWMas3+1 ELSE NULL END as posWMas, CASE WHEN RL.base_class = '17' THEN @curRowJun3 := @curRowJun3+1 ELSE NULL END as posJun, CASE WHEN RL.base_class = 'W17' THEN @curRowWJun3 := @curRowWJun3+1 ELSE NULL END as posWJun, RL.*, CASE WHEN rank_order2 = 10000 THEN CONCAT('No ',rank_string_type,' rank ',rank_string_year) ELSE CONCAT(rank_order2,' 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, CAST(NULL AS UNSIGNED) AS rank_order, CAST(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 UNSIGNED) AS rank_order2 FROM ( SELECT CAST(CASE WHEN ( CASE WHEN EP.reg_class IS NOT NULL AND EP.reg_class>'' THEN EP.reg_class ELSE CASE WHEN P.default_race_class IS NOT NULL AND P.default_race_class>'' THEN P.default_race_class ELSE P.class END END ) = 'PRO' THEN '1. PRO' ELSE '9. NOT VALID REGISTRATION' END AS CHAR(20)) AS base_rank_class, CASE WHEN ( CASE WHEN EP.reg_class IS NOT NULL AND EP.reg_class>'' THEN EP.reg_class ELSE CASE WHEN P.default_race_class IS NOT NULL AND P.default_race_class>'' THEN P.default_race_class ELSE P.class END END ) = 'PRO' THEN 'PRO' 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, PS.icon, 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 ='1047' ) 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 @curRowPro3 := 0, @curRowAm3 := 0, @curRowWo3 := 0, @curRowMas3 := 0, @curRowWMas3 := 0, @curRowJun3 := 0, @curRowWJun3 := 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 ) AS RESULT3) ) AS TOT ORDER BY class_order, rank_order2, base_rank_class, rank_order