CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
whyiamhere
whyiamhere

Reputation: 35

Select rows which do not have duplicates

I have following SQL table:

+--------------+
| Account      |
+--------------+
|    11100011  | 
|    11100012  | 
|    11100013  |  
|    11100014  | 
|    11100011  |  
|    11100012  | 
|    11100015  | 
+--------------+

What I need is to make a select query with rows that do not have any duplicates in this table (i.e. removing both duplicated rows), so query result will look like this

+--------------+
| Account      |
+--------------+
|    11100013  |  
|    11100014  | 
|    11100015  |
+--------------+

How can I do this in tsql?

Upvotes: 0

Views: 57

Answers (4)

Sreenu131
Sreenu131

Reputation: 2516

Using Window Function also we get the same result

SELECT Account FROM
(
SELECT Account,
     COUNT(Account)OVER(Partition by Account ORDER BY Account) AS DistinctCnt 
FROM cte
)
DT WHERE DT.DistinctCnt=1

Result

Account
--------
11100013
11100014
11100015

Upvotes: 0

Sanjay Kumaar
Sanjay Kumaar

Reputation: 48

Try this, 

select * from table_name group by account having count(*)<=1;

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

select account
from 
table
group by account
having count(*)=1

Upvotes: 5

kvk30
kvk30

Reputation: 1203

SELECT DISTINCT Account
FROM table_name_in_your_database;

General this query is used for selecting distinct elements in the table.

Upvotes: 1

Related Questions