SQL QUERIES

to get all participants that have more than 1 record in the table where at lease one of those records has IsCurrent = 0 and IsActive = 1

SELECT  ParticipantId 
FROM    Contact
WHERE   EXISTS
        (   SELECT  1
            FROM    Contact c2
            WHERE   c2.ParticipantID = c.ParticipantId
            AND     ContactTypeId = 1
            GROUP BY ParticipantID
            HAVING COUNT(*) > 1
            AND COUNT(CASE WHEN IsCurrent = 0 AND IsActive = 1 THEN 1 END) >= 1
        );

get records from north wind database(products table) grouped by categoryid and count >11 and min and max per group 

WITH CTE_Pages2 AS
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY UnitPrice desc) AS ‘Row Number’, ProductName, UnitPrice,CategoryID
  FROM Products
)

SELECT
  A.MINN,B.maxx,A.CategoryID
  from (select categoryid,MIN(unitprice) as minn from CTE_Pages2 group by categoryid HAVING COUNT(categoryid)>11) A
  join
  (select categoryid,max(unitprice) as maxx from CTE_Pages2 group by categoryid HAVING COUNT(categoryid)>11) B
  ON A.CATEGORYID=B.CategoryID

using intersect

SELECT  ParticipantId 
    FROM Contact
   Group by ParticipantId 
  Having Count(*) > 1 
Intersect
  SELECT  ParticipantId 
    FROM Contact
   WHERE IsCurrent = 0 
     AND IsActive = 1 
     AND ContactTypeId = 1

The SQL INTERSECT clause/operator is used to combine two SELECT

statements, but returns rows only from the first SELECT

statement that are identical to a row in the second SELECT

statement. This means INTERSECT returns only common rows returned

by the two SELECT statements.