Majority of Column 1

[Tinkerers]

Hi,

I have a table like this:

ColumnA | ColumnB
C 1
C 1
C 1
C 1
C 3
C 2
D 6
D 6
D 6
D 5

I need to write a query that will show only the values that have the majority of the rows. Example C1, D6, but NOT C2,C3 or D5.
This is much tougher than I thought. The number of rows can differ.

Thanks for any help,
Paul

 

[TheSQL]

How do you define "the majority of rows"?

You could start with

SELECT ColumnA, ColumnB, COUNT(*) AS NbrOfRows
FROM MyTable
GROUP BY ColumnA, ColumnB
ORDER BY Count(*) DESC

 

[PerlyGates]

I thought someone would have a slick answer to this. This is the only thing I could come up which is a bit convoluted.

DECLARE @tv TABLE (
   ColumnA char(1),
   ColumnB int
) 

INSERT INTO @tv
SELECT 'C',            1
UNION ALL
SELECT 'C',            1
UNION ALL
SELECT 'C',            1
UNION ALL
SELECT 'C',            1
UNION ALL
SELECT 'C',            2
UNION ALL
SELECT 'C',            2
UNION ALL
SELECT 'C',            3
UNION ALL
SELECT 'C',            2
UNION ALL
SELECT 'D',            6
UNION ALL
SELECT 'D',            6
UNION ALL
SELECT 'D',            6
UNION ALL
SELECT 'D',            5
UNION ALL
SELECT 'E',            1
UNION ALL
SELECT 'E',            8
UNION ALL
SELECT 'E',            1
UNION ALL
SELECT 'E',            8

      

/* 
   The following will also bring back duplicate 
   ColumnA's i.e. if there are the same number of 
   ColumnB's per ColumnA - E8 and E1 in the 
   example data above (2 occurences each).  
*/ 

 SELECT t1.ColumnA AS columnA 
       ,t1.ColumnB AS columnB
       ,t1.myCount AS finalCount
   FROM (SELECT COUNT(*) AS myCount,
                ColumnA,
                ColumnB 
           FROM @tv
       GROUP BY ColumnA,ColumnB ) t1 
INNER JOIN
        ( SELECT MAX( in1Count ) AS myCount
                ,ColumnA
            FROM( SELECT COUNT(*) AS in1Count,
                         ColumnA,
                         ColumnB 
                    FROM @tv
                GROUP BY ColumnA,ColumnB ) in1
          GROUP BY ColumnA ) t2 
   ON t1.ColumnA              = t2.ColumnA
  AND t1.myCount              = t2.myCount 



/* 
   The following will only bring back unique ColumnA
   values.   
*/ 


 SELECT t1.ColumnA columnA 
       ,MIN(t1.ColumnB) columnB
       ,MAX(t1.myCount) finalCount
   FROM (SELECT COUNT(*) AS myCount,
                ColumnA,
                ColumnB 
           FROM @tv
       GROUP BY ColumnA,ColumnB ) t1 
INNER JOIN
        ( SELECT MAX( in1Count ) AS myCount
                ,ColumnA
            FROM( SELECT COUNT(*) AS in1Count,
                         ColumnA,
                         ColumnB 
                    FROM @tv
                GROUP BY ColumnA,ColumnB ) in1
          GROUP BY ColumnA ) t2 
   ON t1.ColumnA              = t2.ColumnA
  AND t1.myCount              = t2.myCount 
GROUP BY t1.ColumnA

 

[SQLSister]

It's not pretty but it works

SELECT ColumnA, ColumnB, COUNT(*) AS NbrOfRows into #temp
FROM table
GROUP BY ColumnA, ColumnB

select t.* from #temp t join
(select columna, Max(NbrOfRows) as NBr from #temp group by columna) a 
on a.columna = t.columnA and a.nbr = t.NbrOfRows

Note this will give you two (or more) rows for a value in columnA if they have the same count. You would need some other way of selecting the one you want in that case if you only want one row. You could do it without the temp table but then you get a derived table inside a derived table and it gets harder to follow.

"NOTHING is more important in a database than integrity." ESquared

 

[TheSQL]

How about:
--set up the table to test
CREATE TABLE MYTABLE
(ColumnA char(1), ColumnB int)

INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',1)
INSERT MyTable VALUES ('C',3)
INSERT MyTable VALUES ('C',2)
INSERT MyTable VALUES ('D',6)
INSERT MyTable VALUES ('D',6)
INSERT MyTable VALUES ('D',6)
INSERT MyTable VALUES ('D',5)

GO
--actual script
SELECT a.ColumnA, a.ColumnB
FROM (SELECT ColumnA, ColumnB, Count(*) AS NbrOfRows
FROM MyTable
GROUP BY ColumnA, ColumnB) a
JOIN (SELECT MAX(x.NbrOfRows) AS MaxRows
FROM
(SELECT ColumnA, ColumnB, Count(*) AS NbrOfRows
FROM MyTable
GROUP BY ColumnA, ColumnB) AS x) as b ON a.NbrOfRows = b.MaxRows

 

[Tinkerers]

Thankyou to all who went after this brain teaser. Wow, for such a simple thing, it turned into a quite complex query. I did use the version from SQLSister, as it was the shortest and easiest.
SELECT ColumnA, ColumnB, COUNT(*) AS NbrOfRows into #temp
FROM table
GROUP BY ColumnA, ColumnB

select t.* from #temp t join
(select columna, Max(NbrOfRows) as NBr from #temp group by columna) a
on a.columna = t.columnA and a.nbr = t.NbrOfRows