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

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY EmployeeID) AS RowNum
    FROM Employees
) AS Duplicates
WHERE RowNum > 1;

Explanation: Here we use partition by email for grouping the data.
Then we are using Order By to order the data of column EmployeeID descending order and condition is where rn > 1.

Answer 2

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.

Answer 3

SELECT t1.Col1, t1.Col2, t1.Col3

FROM mytable t1

JOIN (

    SELECT Col1

    FROM mytable

    GROUP BY Col1

    HAVING COUNT(*) > 1

) t2 ON t1.Col1 = t2.Col1;

Explanation:

The query separates rows with duplicate values in Col1. 

It uses a subquery to find duplicates and joins them with the main table (my table).