Select rows that contains unique value ( non-redundant)-CodePudding

Given this table:

id	Name	date
01	B	2022-02-22
01	C	2022-02-20
01	K	2022-02-21
04	T	2022-02-16
05	A	2022-02-15
05	S	2022-02-09
06	D	2022-02-02

I want to select the rows where Id doesn't repeated like:

id	Name	date
04	T	2022-02-16
06	D	2022-02-02

How I can do that with SQL query please.

CodePudding user response：

with cte as 
(
    select id, count(Name) as cnt
    from TableName
    group by id
)
select cte.id, tableName.Name
from cte 
inner join TableName on
cte.id = tableName.id
where cte.cnt = 1

CodePudding user response：

select id,name from test
where ID NOT IN ( select id from test group by id having count(*)> 1)

CodePudding user response：

select * from test where id not in
(select distinct id from 
(select *,row_number () 
 over (partition by id order by Name asc) rn 
from test
) b
where rn>1)

CodePudding user response：

CREATE TABLE test (
  id INT,
  name CHAR,
  date DATE
);
INSERT INTO test VALUES (1, 'A', '2022-02-22');
INSERT INTO test VALUES (1, 'B', '2022-02-21');
INSERT INTO test VALUES (2, 'C', '2022-02-20');
INSERT INTO test VALUES (3, 'D', '2022-02-19');

QUERY

select * from test
where id in (select id from test group by id having count(*) = 1)

OUTPUT

id  name date
2   C    2022-02-20
3   D    2022-02-19

db-fiddle

CodePudding user response：

Assuming the table name is Test. The below query would work.

Select *
From Test
Group by Id
Having count(*) = 1