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
How to Force Evaluation of Subquery Before Joining/Pushing Down to Foreign Server
Add 2 Months to Current Timestamp
SQL Find Sets with Common Members (Relational Division)
SQL Server Management Studio 2012 - Export/Import Data From/To Table
Postgres Copy from Variable with CSV Data
Hebrew Encoding in SQL Server2005
Stored Procedure Failing on a Specific User
Insert Blank Row Between Groups of Rows and Sorted by Id in SQL
How to Extend the Query to Add 0 in the Cell When No Activity Is Performed
Callablestatement + Registeroutparameter + Multiple Row Result
Compare Deleted and Inserted Table in SQL Server 2008
How to Specify SQL Sort Order in SQL Query
Rename a Column in All the Tables - SQL
How to Get Rows Having Sum Equal to Given Value
T-SQL Looping Through Xml Data Column to Derive Unique Set of Paths