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

Mysql – How to filter out certain rows with aggregation

I am a sql newbie.
I have data in a mysql db that looks like

col1. col2. col3. col4. col5.
a b true true c
a d false false e
a b false false c
a f false false g
a b false false c
i b false false j
a d true true e

I would like to write a query that

a) finds all rows that have col3 and col4 both set to true (so the first and last rows of the table above)

b) finds all rows that have the same col1 and col2 as the rows in a) (so the 3rd and 5th row have the same col1 and col2 as the first row, and the 2nd row has the same col1 and col2 as the last row)

c) Removes the rows in a) and b) returning all other rows

So the result returned by the query would be

col1. col2. col3. col4. col5.
a f false false g
i b false false j

For a) I could create a sub-table like

select col1, col2, col3, col4, col5 from the_table as a_table where col3=’true’ and col4=’true’

For b) I probably would have to do a lookup into a)

Then c) would subtract the result of b) from the full table

Is there a quicker way with group by?

Tags:

3

Answers


  1. 0

    You can use a subquery to identify the col3 & col4 = true conditions, then by using a left join to that subquery joining via cols 1 & 2 you find all the matching rows. Then finally, filter for only the unmatched rows via an IS NULL condition:

    SELECT t.*
FROM the_table t
LEFT JOIN (
    SELECT col1, col2
    FROM the_table
    -- cols 3 & 4 = true
    WHERE col3 = 'true' AND col4 = 'true'
          --  match these rows via cols 1 & 2
    ) t34 ON t.col1 = t34.col1 AND t.col2 = t34.col2
-- now return only the unmatched rows
WHERE t34.col1 IS NULL
    Login or Signup to reply.
  2. 0

    You can simply use NOT EXISTS as follows:

    SELECT * FROM YOUR_TABLE T
WHERE NOT EXISTS 
 (SELECT 1 FROM YOUR_TABLE TT
  WHERE TT.COL1 = T.COL1 AND TT.COL2 = T.COL2
    AND TT.COL3 = 'true' AND TT.COL4 = 'true');
    Login or Signup to reply.
  3. 0

    Yes, aggregation can help here. You want to find at col1/col2 groups for which not exists a col3=true/col4=true row.

    MAX(col3 and col4) OVER (PARTITION BY col1, col2)

    gives you a 1 for a col1/col2 group that has a col3=true/col4=true row, and 0 for the other groups, because in MySQL true=1, false=0. Thus MAX(<bool expression>)gives you a 1, if at least one row matches the condition and false otherwise.

    select col1, col2, col3, col4, col5
from
(
  select t.*, max(col3 and col4) over (partition by col1, col2) as flag
  from mytable t
) checked
where flag = 0;

    This must be done in a subquery, because window functions (MAX OVER here) are executed last in a query. There exist DBMS that have a QUALIFY clause for this that comes after window functions in execution order, but MySQL doesn’t feature this clause.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search