MySQL Find_In_Set statement assistance

Question

I have the following MySQL code in my Select query

FIND_IN_SET(`Final Total`, (
SELECT GROUP_CONCAT( DISTINCT `Final Total`
ORDER BY `Final Total` ASC ) FROM `Dressage scores`)) AS `Final Rank`,

And I am trying to figure out a way to stop the code from executing if it sees a W or an E in the Final Rank field for that record, but execute on all other records in the table.

so I tried testing with something like this

IF (`Final Rank` == "E") THEN FIND_IN_SET(`Final Total`, (
SELECT GROUP_CONCAT( DISTINCT `Final Total`
ORDER BY `Final Total` ASC ) FROM `Dressage scores`)) AS `Final Rank`,

I have also tried something like this

FIND_IN_SET(`Final Total`, (
SELECT GROUP_CONCAT( DISTINCT `Final Total`
ORDER BY `Final Total` ASC ) FROM `Dressage scores`)) AS `Final Rank` WHERE `Final Rank` <> "E",

But I obviously need some help.

This is for a competition scoring system, so if the certain record withdraws (W) or gets eliminated (E) i want to manually be able to insert a W or E in the Final Rank field, and then have the system only rank the records that do not contain a W or E.

Can anyone help me out with this please?

Answer 1

Let's speed it with

EXISTS( SELECT 1 FROM `Dressage scores`
            WHERE `Final Total` = `outer table`.`Final Total` )

I'm confused on your "final rank" test. Maybe:

IF (FIND_IN_SET(`Final Rank`, "E,W"),
    false,
    EXISTS( SELECT 1 FROM `Dressage scores`
            WHERE `Final Total` = `outer table`.`Final Total` )
   )
    

Note that MySQL uses =, not ==.

Apparently, you want a true/false value returned from the expression? That is what your expressions are doing, so is mine.

EXISTS( SELECT 1 ...) is fast because it stops with TRUE when it finds a match -- without scanning the entire table or building a temporary table. ORDER BY and DISTINCT are extra, unnecessary, effort.

EXISTS returns true/false.

IF(boolean-test, value if true, value if false) -- both the values in my example are true/false.

I am pretty sure IF() "short circuits". Another possible short-circuit:

NOT FIND_IN_SET(`Final Rank`, "E,W")
 OR EXISTS( SELECT 1 FROM `Dressage scores`
            WHERE `Final Total` = `outer table`.`Final Total` )

For further discussion, please provide a small set of cases of the data, plus the desired true/false for each case.