Exclude Rows Based on Other Rows (Sql)

Exclude rows based on other rows (SQL)

Use:

SELECT * 
  FROM TABLE a
 WHERE a.val IN (1,2,3)
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE b
                   WHERE b.id = a.id
                     AND b.val NOT IN (1, 2, 3))

Exclude rows having similar columns with another row(s)

In SQL you'd use LEAST and GREATEST for this, provided your DBMS supports these functions:

select
  min(id),
  least(baseAsset, quoteAsset) as asset1,
  greatest(baseAsset, quoteAsset) as asset2,
from mytable
group by least(baseAsset, quoteAsset), greatest(baseAsset, quoteAsset)
order by min(id);

In standard SQL you can replace above LEAST by

CASE WHEN baseAsset < quoteAsset THEN baseAsset ELSE quoteAsset END

and above GREATEST by

CASE WHEN baseAsset > quoteAsset THEN baseAsset ELSE quoteAsset END

Another simple approach is to use NOT EXISTS:

select *
from mytable
where not exists
(
  select null
  from mytable reverse
  where reverse.baseAsset = mytable.quoteAsset
  and reverse.quoteAsset = mytable.baseAsset
  and reverse.id < mytable.id
);

SQL - Exclude rows from SELECT statement if a certain column combination exists in that row

Since a row can only match one of those conditions at one time, you should be using OR:

SELECT COLUMN1, COLUMN2, COLUMN3
FROM YourTable
WHERE NOT (    
    ( COLUMN2 = 'A' AND COLUMN3 = 'B' )
     OR 
    ( COLUMN2= 'B' AND COLUMN3 = 'C' )
)

db<>fiddle here

Exclude rows if a column has different values for a combination of other columns

You can do this with a combination of GROUP BY and CAST. First you can look for c1 and c2 combinations that occur only once, then you can filter for combinations that have a c3 of false.

SELECT c1, c2, MIN(CAST(c3 AS INT)) AS c3
FROM YourTable
GROUP BY c1, c2
HAVING COUNT(DISTINCT c3) = 1 AND MIN(CAST(c3 AS INT)) = 0

SQL Server : excluding rows from a table IF there is another row with the same col value and a particular secondary column value

You could use a not exists clause, to filter out any rows that have the same account number as a row with status 0. Something along the lines of the following:

select * 
from table1 t1
where not exists (
  select account
  from table1 t2
  where t2.account=t1.account
    and t2.status=0
)

A slightly different approach would be:

select *
from table1 t1
where account not in (
  select distinct account
  from table1 t2
  where t2.status=0 )

How to exclude rows which satisfies multiple conditions in sql?

You want:

SELECT *
FROM Table 
WHERE NOT (call_count = 3 AND call_duration = 0);

By the DeMorgan's Laws, this can be rewritten as :

SELECT *
FROM Table 
WHERE call_count <> 3 OR call_duration <> 0;

I would probably use the first version in practice, as it closely resembles your English language requirements.

Excluding rows based on column

You just have to add, this will exclude the records where you see more than 1 ids.

and id2 not in (Select id2 from table1 group by id2 having count(*) > 1)

Similarly add for id1 with OR

Excluding Rows Based on Column Contents

Sounds like a WHERE NOT EXISTS may work here,

SELECT Phone, Name, Age
FROM Customer C
WHERE NOT EXISTS (
    SELECT * FROM Customer M
    WHERE M.Age = 45 AND M.Phone = C.Phone
)

---

Related Topics