Se podria hacer un con join a la misma tabla usando las 2 columnas algo como esto:
Código SQL:
Ver originalCREATE TABLE #temp
(
col1 INT,
col2 INT
)
INSERT INTO #temp VALUES (3, 6)
INSERT INTO #temp VALUES (3, 17)
INSERT INTO #temp VALUES (3, 18)
INSERT INTO #temp VALUES (4, 6)
INSERT INTO #temp VALUES (4, 18)
INSERT INTO #temp VALUES (5, 6)
INSERT INTO #temp VALUES (5, 18)
INSERT INTO #temp VALUES (8, 17)
INSERT INTO #temp VALUES (12, 17)
INSERT INTO #temp VALUES (12, 18)
INSERT INTO #temp VALUES (13, 6)
INSERT INTO #temp VALUES (13, 17)
SELECT test.col2 FROM(
SELECT t1.col2,COUNT(t1.col2) AS total FROM #temp AS t1
INNER JOIN #temp AS t2 ON (t1.col1=t2.col1 AND t1.col2=t2.col2)
WHERE t1.col1 IN (3,4,5)
GROUP BY t1.col2
) AS test WHERE total=(
SELECT MAX(total) FROM(
SELECT t1.col2,COUNT(t1.col2) AS total FROM #temp AS t1
INNER JOIN #temp AS t2 ON (t1.col1=t2.col1 AND t1.col2=t2.col2)
WHERE t1.col1 IN (3,4,5)
GROUP BY t1.col2
) AS completa)
un poco rebuscado pero da los valores que ocupas
col2
6
18