Find rows with duplicate entries in SQL Server

Question

I need to find rows with duplicate order numbers in a table in SQL Server.

For example SELECT * shows:

I want to get

No
------
5003
5003
5003
5006
5006

Is it possible to write a query to do that?

You haven't found anything in your research about duplicates? — Tim Schmelter, Jul 10 '15 at 15:49
possible duplicate of [Select statement to find duplicates on certain fields](http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields) — Kritner, Jul 10 '15 at 15:53

score 0 · Answer 1 · answered Jul 10 '15 at 15:51

0

Although this question was answered thousand times on SO, use GROUP BY + HAVING:

SELECT No
FROM dbo.tablename
Group By No
Having Count(*) > 1

answered Jul 10 '15 at 15:51

Tim Schmelter

450,073
74
686
939

score 0 · Answer 2 · answered Jul 10 '15 at 16:03

0

SELECT S.No
  FROM [dbo].[Shows] S
  INNER JOIN (
      SELECT [No]
      FROM [dbo].[Shows]
      GROUP BY [No]
      HAVING COUNT(*) > 1
  ) J
    ON S.No = J.No

answered Jul 10 '15 at 16:03

Yuriy Rypka

1,937
1
19
23

But I can't imagine why do you want this subset of data:) – Yuriy Rypka Jul 10 '15 at 16:06

score 0 · Answer 3 · answered Jul 10 '15 at 16:19

0

You can use COUNT() OVER() to get count of rows per No and, in an outer query, filter out all non-duplicate rows:

SELECT [No]
FROM (
   SELECT [No], COUNT(*) OVER (PARTITION BY [No]) AS cnt
   FROM mytable ) t
WHERE cnt > 1

Demo here

answered Jul 10 '15 at 16:19

Giorgos Betsos

71,379
9
63
98

Find rows with duplicate entries in SQL Server

3 Answers3