Sean Lange (9/26/2013)
Thanks to hunchback for the ddl and data. Here is another way to do the same thing.
select Rate
from @T
cross apply (select count(*) as RowsCount from @t where rate = @Rate) r
group by Rate
having COUNT(*) >= max(r.RowsCount)
Sean - Should this return Rate = 9? I think your approach simply returns any Rate that has 3 or more rows.
DECLARE @T TABLE (
Rate int NOT NULL,
Shift int NOT NULL,
PRIMARY KEY (Rate, Shift)
);
INSERT INTO @T (
Rate,
Shift
)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 5),
(3, 1),
(3, 2),
(3, 3),
(8, 4),
(4, 1),
(4, 2),
(5, 1),
(5, 2),
(5, 3),
(5, 4),
(6, 1),
(7, 2),
(8, 2),
(9, 2),
(9, 3),
(9, 4);
DECLARE @Rate int = 1;
select Rate
from @T
cross apply (select count(*) as RowsCount from @t where rate = @Rate) r
group by Rate
having COUNT(*) >= max(r.RowsCount)
I think this works as the OP requested also:
SELECT 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);
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St