CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

Return all rows/columns where multiple columns are non distinct

I have a query where I want to find rows where both ActivityDate and TaskId have multiple entries at the same time:

SELECT
    ActivityDate, taskId
FROM
    [DailyTaskHours]
GROUP BY
    ActivityDate, taskId
HAVING 
    COUNT(*) > 1

The above query appears to work. However I want all of the columns to return now just the two (ActivityDate, taskId). This doesn't work:

SELECT *
FROM
    [DailyTaskHours]
GROUP BY
    ActivityDate, taskId
HAVING 
    COUNT(*) > 1

because many of the columns are not in the group by clause. I don't want any columns to be effected by the HAVING COUNT(*) > 1 other than ActivityDate, taskId.

How do I achieve this?

Upvotes: 1

Views: 165

Answers (4)

Linger
Linger

Reputation: 15058

Here is the SQL Fiddle that shows the following query in action:

SELECT DISTINCT m.* 
FROM 
(
  SELECT s.ActivityDate, s.taskId
  FROM DailyTaskHours s
  GROUP BY s.ActivityDate, s.taskId
  HAVING COUNT(*) > 1
) sub
JOIN DailyTaskHours m 
ON m.taskId = sub.taskId
AND m.ActivityDate = sub.ActivityDate

Upvotes: 0

laylarenee
laylarenee

Reputation: 3284

-- fully functional example.
DECLARE @table TABLE ( ActivityDate DATE, TaskID INT);

SET NOCOUNT ON;
INSERT @table VALUES ('01/01/2013',1);
INSERT @table VALUES ('01/02/2013',1);
INSERT @table VALUES ('01/02/2013',2);
INSERT @table VALUES ('01/03/2013',1);
INSERT @table VALUES ('01/03/2013',2);
INSERT @table VALUES ('01/03/2013',5); -- duplicate date,taskid
INSERT @table VALUES ('01/03/2013',5); -- duplicate date,taskid
SET NOCOUNT OFF;

SELECT A.*
FROM @table A
    INNER JOIN (
        SELECT [ActivityDate], TaskId
        FROM @table
        GROUP BY [ActivityDate], TaskId
        HAVING Count(*) > 1
    ) AS B ON B.[ActivityDate]=A.ActivityDate AND B.TaskId=A.TaskId;

Upvotes: 0

Anon
Anon

Reputation: 10908

SELECT t1.*
FROM
    [DailyTaskHours] t1
INNER JOIN (
  SELECT
      ActivityDate, taskId
  FROM
      [DailyTaskHours]
  GROUP BY
      ActivityDate, taskId
  HAVING 
      COUNT(*) > 1
) t2 ON (
  t1.ActivityDate = t2.ActivityDate AND
  t1.taskId = t2.taskId
)

Upvotes: 1

FrankPl
FrankPl

Reputation: 13315

WITH sel as(
SELECT
    ActivityDate, taskId
FROM
    [DailyTaskHours]
GROUP BY
    ActivityDate, taskId
HAVING 
    COUNT(*) > 1
)
SELECT * 
      FROM [DailyTaskHours] d
           INNER JOIN sel ON d.ActivityDate = sel.ActivityDate AND d.taskId = sel.taskId

Upvotes: 2

Related Questions