CODEWITHSUNDEEP
sqlsql-server
Yousef
Yousef

Reputation: 403

Group by and Count to select repeated rows

I wrote this query but it does not work as I expected.

1st Goal: select rows that have repeated in certain columns and return whole columns.

2nd Goal: Update a flag (a column) to identify which records have repeated.

Could you please help me?

SELECT
  *
FROM AvvalV2NS AS M
WHERE EXISTS 

(SELECT
  M.Astate,
  M.Acity,
  M.Azone,
  M.Abvillage,
  M.Avillage,
  COUNT(*)
FROM AvvalV2NS AS M
GROUP BY M.Astate,
         M.Acity,
         M.Azone,
         M.Abvillage,
         M.Avillage
HAVING COUNT(*) > 1)

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

If you want to get the rows that are duplicated, window functions are probably the easiest way:

select a.*
from (select a.*,
             count(*) over (partition by M.Astate, M.Acity,  M.Azone, M.Abvillage, M.Avillage) as cnt
      from AvvalV2NS a
     ) a
where cnt > 1;

You can update a flag by doing something like this:

with toupdate as (
      select a.*
      from (select a.*,
                   count(*) over (partition by M.Astate, M.Acity,  M.Azone, M.Abvillage, M.Avillage) as cnt
            from AvvalV2NS a
           ) a
     )
update toupdate
    set isduplicate = (case when cnt > 1 then 1 else 0 end);

Upvotes: 1

yılmaz
yılmaz

Reputation: 419

Suppose your table have an id column: 

SELECT * FROM THE_TABLE WHERE ID IN (
    SELECT ID FROM 
    (SELECT ID, REPEATING_COLUMNS, COUNT(*) FROM THE_TABLE GROUP BY REPEATING_COLUMNS HAVING COUNT(*) > 1)
)

UPDATE THE_TABLE SET THE_FLAG = "HERE WE GO" WHERE ID IN (
    SELECT ID FROM 
    (SELECT ID, REPEATING_COLUMNS, COUNT(*) FROM THE_TABLE GROUP BY REPEATING_COLUMNS HAVING COUNT(*) > 1)
)

Hope this helps.

Upvotes: 0

Related Questions