Select Except the duplicate Records from the table in SQL Server

Question

I have a SQL Server table that has duplicate entries in one of the columns e.g.:

 +----+-----------+------------+ | id | object_id | status_val | +----+-----------+------------+ | 1 | 1 | 0 | | 2 | 1 | 0 | | 3 | 1 | 0 | | 4 | 2 | 0 | | 5 | 3 | 0 | | 6 | 4 | 0 | | 7 | 4 | 0 | +----+-----------+------------+

I need the output to be like this:

 +----+-----------+------------+ | id | object_id | status_val | +----+-----------+------------+ | 4 | 2 | 0 | | 5 | 3 | 0 | +----+-----------+------------+

How to resolve this?

i think Matt & yossi answer are good so far.

KumarHarsh
– KumarHarsh

2017-12-05 11:09:13 +00:00
Commented Dec 5, 2017 at 11:09 — KumarHarsh
– KumarHarsh, Commented Dec 5, 2017 at 11:09

mortb · Accepted Answer · 2017-12-05 10:47:21Z

Is this what you are looking for?

SELECT * FROM <yourTable> t1 WHERE t1.object_id NOT IN ( SELECT t2.object_id FROM <yourTable> t2 GROUP BY t2.object_id HAVING COUNT(object_id) > 1 )

Yossi Vainshtein · Accepted Answer · 2017-12-05 10:46:52Z

Try this:

select min(id), object_id, min(status_val) from table group by object_id having count(*) = 1

Matt · Accepted Answer · 2017-12-05 10:52:52Z

Use HAVING and GROUP BY

SELECT MIN(id) id, object_id, MIN(status_val) status_val FROM yourtable GROUP BY object_id HAVING COUNT(object_id) = 1

Output

id object_id status_val 4 2 0 5 3 0

SQL Fiddle: http://sqlfiddle.com/#!6/7f643f/9/0

Subhash Prajapati · Accepted Answer · 2017-12-05 10:55:26Z

You can use group by for unique record like below :-

SELECT * from TABLENAME group by TABLE_COLOUM_NAME

This query give you only unique value from your Table.

Ullas · Accepted Answer · 2017-12-05 11:04:48Z

Give a row number for each row partitioned and ordered by the columns [object_id], [status_val]. Then from the result set select the rows which having maximum row number 1.

Query

;with cte as( select [rn] = row_number() over( partition by [object_id], [status_val] order by [object_id], [status_val] ), * from [your_table_name] ) select min([id]) as [id], [object_id], [status_val] from cte group by [object_id], [status_val] having max([rn]) = 1;

Find a demo here

Bora Erbasoglu · Accepted Answer · 2017-12-05 11:30:18Z

SELECT COUNT(*) FROM( SELECT DISTINCT object_id FROM object_table ) as row_count, status_val,id,object_id FROM object_table where row_count = 1;

I think you are looking for that

Collectives™ on Stack Overflow

Select Except the duplicate Records from the table in SQL Server

6 Answers 6

Comments

Comments

Comments

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

1 Comment

Related