I have a SQL query result I would love to clean up, the query result is following
NAME | OPT_IN |
---|---|
JOHN | TRUE |
JOHN | FALSE |
KEK | TRUE |
and now I want to select | JOHN | FALSE |
when | JOHN | TRUE |
edit: adding the expected result, and fixed a few wordings with more correct terms
NAME | OPT_IN |
---|---|
JOHN | FALSE |
KEK | TRUE |
The way I have tried is highly inefficient, I first get all the columns with TRUE
and all the columns with FALSE
and then post processing them using Python.
4
Answers
You may use this delete query.
You can use a simple delete statement for this. First filter for all records where
opt_in
is true, then useEXISTS
to also filter for those that also have a corresponding false record. e.g.Note the subquery (
(SELECT * FROM t)
) in the exists is due to a limitation in MySQL about referencing the same table you are deleting from directly in the where clause, so a further level of nesting is required to get around this.Example on db<>fiddle
ADDENDUM
If you just want to perform this filter in a select, you can still use exists (or not exists to be more precise), you just need to rearrange the logic slightly:
Example on db<>fiddle
We can retrieve the ones with both FALSE and TRUE values using
GROUP BY
,MIN
andMAX
, the condition is that there is at least one record with "opt_in" set to FALSE and at least one record set to TRUE :Then we join the table with this dataset, and remove the matched entries :
Demo here
You can use a window function. This is likely to be more efficient than the other options which do a self-join
Either just sort and take the top 1 per group
Or a conditional count.