skip to Main Content

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


  1. 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. 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. 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. 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