How to skip rows with same column values in SQL Server?

iklwldmw  于 2023-05-21  发布在  SQL Server
关注(0)|答案(6)|浏览(160)

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?

nfg76nw0

nfg76nw01#

You can use a similar logic:

SELECT * 
FROM TABLE 
WHERE COLB NOT IN (SELECT COLB 
                   FROM TABLE 
                   GROUP BY COLB 
                   HAVING COUNT(*) > 1)
wwodge7n

wwodge7n2#

With NOT EXISTS:

select * from tablename t
where not exists (
  select 1 from tablename
  where colb = t.colb and cola <> t.cola
)
emeijp43

emeijp433#

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.

umuewwlo

umuewwlo4#

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
l3zydbqr

l3zydbqr5#

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 |
+------+------+-------+

Live Demo

mzmfm0qo

mzmfm0qo6#

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];

相关问题