Get all the rows where a certain column does not repeat-CodePudding

I have a table in SQL that looks like this:

CREATE TABLE Color (
    id int,
    name varchar(50)
);

INSERT INTO
    Color
VALUES
    (1,  'Blue'),
    (2,   'Red'),
    (3, 'Black'),
    (4, 'Black'),
    (5, 'White'),
    (6, 'White');

I need a query to show only the rows where the color does not repeat in any other row.

Expected result table:

ID	Color
1	Blue
2	Red

I tried selecting the desired result table doing this:

Select c3.id, c2.name
FROM (
    SELECT name, count(*)
        FROM color c1
        GROUP BY name
        HAVING count(*) = 1
    ) c2 INNER JOIN color c3 on c3.name = c2.name

CodePudding user response：

You can use not exists

select id, name
from Color C1
where not exists (select 1 from Color C2 where C2.name = C1.name and C2.id <> C1.id);

CodePudding user response：

SELECT MAX(id) as id, name 
FROM color
GROUP BY name
HAVING count(id)=1

CodePudding user response：

You could use window function

SELECT
  id,
  name
FROM (
    SELECT *,
      count = COUNT(*) OVER (PARTITION BY name)
    FROM Color c
)
WHERE count = 1;

Alternatively, and probably more performant:

SELECT
  id,
  name
FROM (
    SELECT *,
      nextId = LEAD(id) OVER (PARTITION BY name ORDER BY id),
      prevId =  LAG(id) OVER (PARTITION BY name ORDER BY id)
    FROM Color c
)
WHERE nextId IS NULL AND prevId IS NULL;