In MS Sql-server, how to view additional columns of duplicates?

Question

Say I have a table with column key and column date. I'd like to view entries with duplicate key values along with their date. The below query will give the duplicate key values, but gives an error if I add date to my SELECT statement. How can I view the date of the duplicates?

SELECT key, count(*) FROM tbl 
GROUP BY
  KEY
HAVING 
 count(*) > 1

Possible duplicate of [Finding duplicate rows in SQL Server](http://stackoverflow.com/questions/2112618/finding-duplicate-rows-in-sql-server) — Paddy, Feb 25 '16 at 15:52
@Paddy, I'm sure there's a duplicate somewhere on the site, but that isn't it. — Tab Alleman, Feb 25 '16 at 16:33

Paddy · Accepted Answer · 2016-02-25T16:22:32.703

3

You can use a subquery to achieve this:

SELECT *
FROM tbl
WHERE Key IN 
(
    SELECT key FROM tbl 
    GROUP BY Key
    HAVING COUNT(*) > 1
)

edited Feb 25 '16 at 16:22

answered Feb 25 '16 at 15:51

Paddy

33,309
15
79
114

score 0 · Answer 2 · edited Feb 26 '16 at 00:35

0

Try this :

SELECT * FROM tbl t
where exists
  (
    select 1 FROM tbl tt
    where tt.key = t.key
    GROUP BY KEY
    HAVING count(*) > 1
  )

edited Feb 26 '16 at 00:35

John Slegers

45,213
22
199
169

answered Feb 25 '16 at 15:51

Ivan Starostin

8,798
5
21
39

In MS Sql-server, how to view additional columns of duplicates?

2 Answers2