CODEWITHSUNDEEP
sqlsql-server
user14085796
user14085796

Reputation:

Select the non repeating/Distinct value in SQL

I'm trying to select the record based on the distinct id. When i go for 'DISTINCT' it picks the duplicate record and truncates the repeating record and gives me the one left out. How can i SQL to pick to just that record which isn't repeated ?

INPUT

id name age location
1 a 22 usa
1 a 23 usa
2 b 44 uk
3 e 33 eu
3 f 55 eu
8 k 49 usa

OUTPUT

id name age location
2 b 44 uk
8 k 49 usa

Upvotes: 0

Views: 778

Answers (4)

Kemal AL GAZZAH
Kemal AL GAZZAH

Reputation: 1037

You can use SQL Common Transaction Expression (CTE) AS FOLLOWS

declare @mytable as table(id int ,name nvarchar(100),age int,location nvarchar(50))

insert into @mytable values
(1,'a',22,'usa'),(1,'a',23,'usa'),(2,'b',44,'uk'),(3,'e',33,'eu'),(3,'f',55,'Tunisia'),('8','k',49,'Palestine')


with 
cte1 as(select * from @mytable),
cte2 as (select id, count(1) N from @mytable group by id),
cte3 as (select TA.id,TA.name,TA.age,TA.location from cte1 TA inner join cte2 TB on TA.id=TB.id where TB.N=1)
select * from cte3

Upvotes: 0

d0little
d0little

Reputation: 476

This should achieve the output you're after:

SELECT *
FROM yourtable
WHERE id IN (
    SELECT id
    FROM yourtable
    GROUP BY id
    HAVING COUNT(*) = 1)

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

ok , here is how you can do it :

select * from (
   select * , count(*) over (partition by id) cn
   from tablename
) t
where cn = 1

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Try this:

SELECT *
FROM [Input]
WHERE ID IN (
    SELECT ID FROM [Input] GROUP BY ID HAVING COUNT(ID) = 1
)

Upvotes: 1

Related Questions