0

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
ab11
  • 19,770
  • 42
  • 120
  • 207
  • 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

2 Answers2

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
)
Paddy
  • 33,309
  • 15
  • 79
  • 114
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
  )
John Slegers
  • 45,213
  • 22
  • 199
  • 169
Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39