SQL Select duplicate values without grouping

Question

I've checked all the related questions but can't find exactly what I need.

I need to return any data where one particular field is the same for each record, but I cannot use group by because I need to see all the duplicated records, I do not just want a count of them.

I have also tried using group by and having count(field) > 1 and various forms of simple select and other ways of trying to use count() with no luck.

Basically, I will be needing to see everything in the table apart from where one particular field has a unique value. while if it is not unique I need to view it.

There are some additional where requirements but for simplicity, I'm not including them here. I'm using the SSMS 2008 R2.

Example:

Data:

Col1  |  Col2  |  Col3
----------------------
abc      wow      13/05/2016
abc      wow      10/05/2016
def      wow      13/05/2016
ghi      wow      13/05/2016
ghi      wow      10/05/2016
jkl      wow      01/01/2016

Expected result:

Col1  |  Col2  |  Col3
----------------------
abc      wow      13/05/2016
abc      wow      10/05/2016
ghi      wow      13/05/2016
ghi      wow      10/05/2016

I am looking to show anything that does not have a unique value in Col1.

Answer 1

You can use the following query:

SELECT Col1, Col2, Col3
FROM (
  SELECT Col1, Col2, Col3,
         COUNT(*) OVER (PARTITION BY Col1) AS cnt
  FROM mytable) AS t
WHERE t.cnt > 1

The query uses the window version of COUNT aggregate function: the function is applied over Col1 partitions. The outer query filters out records which have a Col1 value that appears only once. 

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.