CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
user957178
user957178

Reputation: 631

How to find the duplicate rows in the table using sql

I have tried this query to get the duplicate records.but I am getting this error.

select * from Codes
where id = 35712 and isactive = 1
group by line_num
having count(*) > 1

I am getting this error.

Msg 8120, Level 16, State 1, Line 1
Column 'Codes.code_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

here code_id is the primary key for this table.

can anybody help me out how to get the code_id which have duplicates in this table.

Thanks

Upvotes: 0

Views: 247

Answers (4)

Andriy M
Andriy M

Reputation: 77687

In SQL Server 2005 and later you can use aggregate window functions:

;WITH counted AS (
  SELECT
    *,
    Cnt = COUNT(*) OVER (PARTITION BY line_num)
  FROM Codes
  WHERE id = 35712
    AND isactive = 1
)
SELECT *
FROM counted
WHERE Cnt > 1

References:

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

It search for duplicated values in line_num column, where code_id is primary key of codes table. I don't know exact table definition so this is a bit of guessing.

select c.code_id, c.line_num, t.qt from codes c
join (
    select line_num, count(*) as qt
    from codes
    where id = 35712 and isActive = 1
    group by line_num
    having count(*) > 1
) as t on t.line_num = c.line_num

First columns returns all code_ids that have duplicated value in line_num (second column), qt - quantity.

Upvotes: 2

minaz
minaz

Reputation: 5780

select count(line_num), 
       line_num 
from   codes 
where  id = 35712 
       and isactive = 1 
group  by line_num 
having count(line_num) > 1

Upvotes: 4

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

select code_id, line_num, count(*) from Codes 
where id = 35712 and isactive = 1 
group by code_id, line_num
having count(*) > 1

Upvotes: 1

Related Questions