I've a table as below:
ColA ColB ColC
````` `````` ``````
1 Steve Rodgers
2 Tony Stark
3 Steve Jobs
Resultant table should look like this:
ColA ColB ColC
````` `````` ``````
2 Tony Stark
Rows with same value in ColB should be removed. How can I delete/exclude such rows?
You can use a similar logic:
SELECT *
FROM TABLE
WHERE COLB NOT IN (SELECT COLB
FROM TABLE
GROUP BY COLB
HAVING COUNT(*) > 1)
With NOT EXISTS:
select * from tablename t
where not exists (
select 1 from tablename
where colb = t.colb and cola <> t.cola
)
where colb = t.colb and (cola <> t.cola OR colc <> t.colc)where colb = t.colb and (cola <> t.cola OR colc <> t.colc) won't work either. For 2 different rows with values 2 Tony Stark the result will contain both these rows.DISTINCT keyword in that case.I just want to note that you can do this with aggregation:
select min(cola) as col1, colb, min(colc) as colc
from t
group by colb
having count(*) = 1;
If the count is 1, then the min() brings back the values in that row.
If you have a covering index with leading column ColB you could also use LAG/LEAD. This can scan the index in order (without needing a sort) and retain rows where ColB is not the same as either of its neighbours
WITH T
AS (SELECT *,
LAG(ColB) OVER (ORDER BY ColB) AS PrevColB,
LEAD(ColB) OVER (ORDER BY ColB) AS NextColB
FROM YourTable)
SELECT *
FROM T
WHERE IIF(ColB IN ( PrevColB, NextColB ), 0, 1) = 1
You can also use
CREATE TABLE T(
Col1 INT,
Col2 VARCHAR(45),
Col3 VARCHAR(45)
);
INSERT INTO T VALUES
(1, 'Steve', 'Rodgers'),
(2, 'Tony', 'Stark' ),
(3, 'Steve', 'Jobs');
SELECT *
FROM T
WHERE 1 = (SELECT COUNT(Col2)
FROM T TT
WHERE T.Col2 = TT.Col2
);
Returns:
+------+------+-------+
| Col1 | Col2 | Col3 |
+------+------+-------+
| 2 | Tony | Stark |
+------+------+-------+
You could use COUNT(*) OVER (PARTITION BY …) to count the similar values in a column and then filter by that count, e.g.:
-- Set up the test data
CREATE TABLE [person] (
[personId] Integer
,[givenName] NVarChar(100)
,[familyName] NVarChar(100)
);
INSERT INTO [person] VALUES
(1, N'Steve', N'Rodgers')
,(2, N'Tony', N'Stark' )
,(3, N'Steve', N'Jobs')
;
-- /Set up the test data
-- Use a common table expression to derive our count
WITH tData AS (
SELECT
*
,COUNT(*) OVER (PARTITION BY [P].[givenName]) AS [givenNameInstances]
FROM [person] AS [P]
)
SELECT
[tD].[personId]
,[tD].[givenName]
,[tD].[familyName]
FROM tData AS [tD]
WHERE [tD].[givenNameInstances] = 1 -- Only return results where the count was 1
;
DROP TABLE [person];
