Ask a question

select "p"."id", "p"."name", "p"."state", "p"."age_group", "p"."overall_rating", "p"."is_div1_rep", "p"."is_international", "p"."date_of_birth", "p"."born_on_or_after", (select b2.runs_scored from "batting_performances" as "b2" where "b2"."player_id" = "p"."id" and "b2"."strike_rate" > 100 and "b2"."runs_scored" >= 100 and "b2"."balls_faced" > 0 order by b2.runs_scored desc, b2.id asc limit 1) as "best_runs", (select b2.balls_faced from "batting_performances" as "b2" where "b2"."player_id" = "p"."id" and "b2"."strike_rate" > 100 and "b2"."runs_scored" >= 100 and "b2"."balls_faced" > 0 order by b2.runs_scored desc, b2.id asc limit 1) as "best_balls", (select case when b2.balls_faced > 0 then round(b2.runs_scored * 100.0 / b2.balls_faced, 1) end from "batting_performances" as "b2" where "b2"."player_id" = "p"."id" and "b2"."strike_rate" > 100 and "b2"."runs_scored" >= 100 and "b2"."balls_faced" > 0 order by b2.runs_scored desc, b2.id asc limit 1) as "best_sr" from "players" as "p" where "p"."name"::text not like '%********%' and "p"."name"::text not like 'Unknown Player_%' and not exists (select * from "player_merge_members" as "pmm" inner join "player_merges" as "pm" on "pm"."id" = "pmm"."merge_id" where "pmm"."player_id" = "p"."id" and "pmm"."player_id" != "pm"."master_id") and (("p"."date_of_birth" > '2011-09-01' and "p"."date_of_birth" <= '2012-09-01') or ("p"."date_of_birth" is null and "p"."born_on_or_after" > '2010-09-01' and "p"."born_on_or_after" <= '2011-09-01')) and ("p"."date_of_birth" > '2008-10-26' or ("p"."date_of_birth" is null and "p"."born_on_or_after" > '2007-10-26')) and exists (select * from "batting_performances" as "bp" where "bp"."player_id" = "p"."id" and "bp"."strike_rate" > 100 and "bp"."runs_scored" >= 100 and "bp"."balls_faced" > 0) order by coalesce(p.overall_rating, 0) desc, "p"."name" asc limit 100 offset 0