Question 319076 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL how to drop rows based on values from two columns

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.

Tags:

4

Answers


  1. 0

    You may use this delete query.

    delete from table_name where (column1, column2) = (select column1, TRUE from table_name where <here is your condition to return JOHN/false records>)
    Login or Signup to reply.
  2. 0

    You can use a simple delete statement for this. First filter for all records where opt_in is true, then use EXISTS to also filter for those that also have a corresponding false record. e.g.

    CREATE TABLE t (name varchar(5), opt_in bit);
INSERT INTO t (name, opt_in) VALUES ('John', 1),('John', 0),('kek', 1);

DELETE t1
FROM   t AS t1
WHERE  t1.opt_in =1
AND    EXISTS 
       (   SELECT  1 
           FROM    (SELECT * FROM t) AS t2 
           WHERE   t2.name =t1.name 
           AND     t2.opt_in = 0
       );

    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:

    SELECT  t1.name, t1.opt_in
FROM    t AS t1
WHERE   NOT EXISTS 
        (   SELECT 1 
            FROM    t AS t2 
            WHERE   t2.name = t1.name 
            AND     t2.opt_in = 0 
            AND     t1.opt_in = 1
        );

    Example on db<>fiddle

    Login or Signup to reply.
  3. 0

    We can retrieve the ones with both FALSE and TRUE values using GROUP BY, MIN and MAX, the condition is that there is at least one record with "opt_in" set to FALSE and at least one record set to TRUE :

    SELECT name
FROM mytable
GROUP BY name
HAVING MIN(opt_in) = FALSE AND MAX(opt_in) = TRUE;

    Then we join the table with this dataset, and remove the matched entries :

    DELETE t 
FROM mytable t
INNER JOIN (
      SELECT name
      FROM mytable
      GROUP BY name
      HAVING MIN(opt_in) = FALSE AND MAX(opt_in) = TRUE
) AS s ON s.name = t.name
WHERE t.opt_in = TRUE;

    Demo here

    Login or Signup to reply.
  4. 0

    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

    SELECT
  t.name,
  t.opt_in
FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY t.opt_in) AS countFalse
    FROM t
) t
WHERE t.rn = 1;

    Or a conditional count.

    SELECT
  t.name,
  t.opt_in
FROM (
    SELECT *,
      COUNT(CASE WHEN t.opt_in = 0 THEN 1 END) OVER (PARTITION BY t.name) AS countFalse
    FROM t
) t
WHERE (t.opt_in = 0 OR t.countFalse = 0);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search