Continuing curiosity regarding this problem drove me to see how the proposed solutions scale, so I built a test harness.

CREATE TABLE #T (

Rate int NOT NULL,

Shift int NOT NULL,

PRIMARY KEY (Rate, Shift)

);

INSERT INTO #T (

Rate,

Shift

)

VALUES

(-1, 1),

(-1, 2),

(-1, 3);

WITH Tally (n) AS

(

SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a CROSS JOIN sys.all_columns b

),

STally (n) AS

(

SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a

)

INSERT INTO #T

SELECT a.n, b.n

FROM Tally a

CROSS APPLY STally b;

DELETE

FROM #T

WHERE (Rate < 20000 AND Shift = 1) OR (Rate > 400000 AND Shift = 3)

OR (RATE BETWEEN 50000 AND 150000 AND Shift = 2);

DECLARE @Rate INT = -1

,@Holder INT

,@StartDT DATETIME;

SELECT @StartDT=GETDATE();

PRINT 'Solution by Hunchback';

SET STATISTICS TIME ON;

WITH C1 AS (

SELECT

Shift

FROM

#T

WHERE

Rate = @Rate

)

SELECT

@Holder=A.Rate

FROM

#T AS A

INNER JOIN

C1 AS B

ON A.Shift = B.Shift

GROUP BY

A.Rate

HAVING

COUNT(*) = (SELECT COUNT(*) FROM C1);

SET STATISTICS TIME OFF;

SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());

SELECT @StartDT=GETDATE();

PRINT 'Solution by Dwain.C';

SET STATISTICS TIME ON;

SELECT @Holder=Rate

FROM #T

WHERE Shift IN (SELECT Shift FROM #T WHERE Rate = @Rate)

GROUP BY Rate

HAVING COUNT(*) = (SELECT COUNT(*) FROM #T WHERE Rate = @Rate);

SET STATISTICS TIME OFF;

SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());

SELECT @StartDT=GETDATE();

PRINT 'Divide and Conquer Solution by Dwain.C';

SET STATISTICS TIME ON;

SELECT Shift

INTO #T1

FROM #T

WHERE Rate = @Rate;

DECLARE @Rows INT = @@ROWCOUNT;

SELECT @Holder=Rate

FROM #T

WHERE Shift IN (SELECT Shift FROM #T1)

GROUP BY Rate

HAVING COUNT(*) = @Rows;

SET STATISTICS TIME OFF;

SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());

SELECT @StartDT=GETDATE();

PRINT 'Solution by Peso/Adam Machanic';

SET STATISTICS TIME ON;

WITH Target AS

(

SELECT Rate, Shift

FROM #T

WHERE Rate = @Rate

)

SELECT @Holder=c.Rate

FROM

(

SELECT Rate, rc=COUNT(*)

FROM #T

GROUP BY Rate

) a

JOIN

(

SELECT rc=COUNT(*)

FROM Target

) b ON b.rc <= a.rc

JOIN #T AS c ON c.Rate = a.Rate

JOIN Target AS d ON d.Shift = c.Shift

GROUP BY c.Rate

HAVING COUNT(*) = MIN(b.rc);

SET STATISTICS TIME OFF;

SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());

GO

DROP TABLE #T;

DROP TABLE #T1;

From the statistics, I get these CPU/Elapsed time results:

Solution by Hunchback

SQL Server Execution Times:

CPU time = 1637 ms, elapsed time = 452 ms.

Solution by Dwain.C

SQL Server Execution Times:

CPU time = 1467 ms, elapsed time = 473 ms.

Divide and Conquer Solution by Dwain.C

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 1169 ms, elapsed time = 301 ms.

Solution by Peso/Adam Machanic

SQL Server Execution Times:

CPU time = 1996 ms, elapsed time = 2235 ms.

And these results are quite similar to the results SELECTED into the 4 results panes for elapsed MS:

460

480

306

2263

Comments:

- I eliminated Sean's solution because I believe he's solving a different problem. But of course since the OP has confirmed that he prefers Sean's solution, I have to assume that both Hunchback and I read the requirements wrong.

- The result for my last suggestion (Peso/Adam Machanic solution) was probably impacted by the <= I had to use in the first JOIN. I believe their solution was based on the no remainder problem posed by Mr. Celko.

I was surprised that Divide and Conquer came in at about 30% faster.