Get the most commonly used values ​​from each column within groups

Question

For each group grouped using the GRP field, I would like to get the most common value in column A and the most common value in column B and potentially do this for many other columns.

Sample data:

GRP | A | B
-----------
Cat | 1 | 1
Cat | 2 | 1
Cat | 3 | 2
Cat | 3 | 3
Dog | 5 | 6
Dog | 5 | 7
Dog | 6 | 7

Expected Result:

GRP | A | B
-----------
Cat | 3 | 1
Dog | 5 | 7

This query achieves this result:

SELECT 
    freq1.GRP,
    freq1.A,
    freq2.B
FROM (
        SELECT
            GRP,
            A,
            ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY COUNT(*) DESC) AS F_RANK
        FROM MyTable
        GROUP BY GRP, A
) AS freq1
INNER JOIN (
        SELECT
            GRP,
            B,
            ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY COUNT(*) DESC) AS F_RANK
        FROM MyTable
        GROUP BY GRP, B
) AS freq2 ON freq2.GRP = freq1.GRP
WHERE freq1.F_RANK = 1 AND freq2.F_RANK = 1

It just doesn’t look very efficient, and even more so if I have to add column C, D, etc.

Is there a better way?

Answer 1

, "", . , , . .

with GroupA as
(
    select Grp
        , A
        , ROW_NUMBER() over(partition by grp order by count(*) desc) as RowNum
    from MyTable
    group by Grp, A
)
, GroupB as
(
    select Grp
        , B
        , ROW_NUMBER() over(partition by grp order by count(*) desc) as RowNum
    from MyTable
    group by Grp, B
)

select a.Grp
    , a.A
    , b.B
from GroupA a
inner join GroupB b on a.Grp = b.Grp and b.RowNum = 1
where a.RowNum = 1;

Answer 2

, , temp:

SELECT  GRP, A, B,
    ROW_NUMBER() OVER (PARTITION BY A ORDER BY GRP, A) ARank, 
    ROW_NUMBER() OVER (PARTITION BY B ORDER BY GRP, B) BRank
INTO #TMP
FROM MyTable

SELECT t1.GRP, 
    (SELECT TOP 1 A FROM #TMP WHERE GRP = t1.Grp ORDER BY ARank DESC) A, 
    (SELECT TOP 1 B FROM #TMP WHERE GRP = t1.Grp ORDER BY BRank DESC) B 
FROM MyTable t1
GROUP BY T1.GRP 

DROP TABLE #TMP

SQL Fiddle

:

CREATE TABLE MyTable
    ([GRP] varchar(3), [A] int, [B] int)
;

INSERT INTO MyTable
    ([GRP], [A], [B])
VALUES
    ('Cat', 1, 1),
    ('Cat', 2, 1),
    ('Cat', 3, 2),
    ('Cat', 3, 3),
    ('Dog', 5, 6),
    ('Dog', 5, 7),
    ('Dog', 6, 7)
;

1:

SELECT  GRP, A, B,
    ROW_NUMBER() OVER (PARTITION BY A ORDER BY GRP, A) ARank, 
    ROW_NUMBER() OVER (PARTITION BY B ORDER BY GRP, B) BRank
INTO #TMP
FROM MyTable

SELECT t1.GRP, 
    (SELECT TOP 1 A FROM #TMP WHERE GRP = t1.Grp ORDER BY ARank DESC) A, 
    (SELECT TOP 1 B FROM #TMP WHERE GRP = t1.Grp ORDER BY BRank DESC) B 
FROM MyTable t1
GROUP BY T1.GRP 

DROP TABLE #TMP

:

| GRP | A | B |
|-----|---|---|
| Cat | 3 | 1 |
| Dog | 5 | 7 |

Answer 3

, , - / . .

SQL :

CREATE TABLE ##fields (id INT IDENTITY(1,1),fieldname VARCHAR(255))
INSERT INTO ##fields
        ( fieldname )
VALUES  ('A'),('B') --Add field names here
DECLARE @maxid INT
SELECT @maxid = MAX(id) FROM ##fields

CREATE TABLE ##Output (GRP VARCHAR(3), A INT, B INT) --Add field names here

INSERT INTO ##Output
        ( GRP )
SELECT DISTINCT GRP FROM MyTable

DECLARE @SQL NVARCHAR(MAX)
DECLARE @i INT = 1

WHILE @i <=@maxid
    BEGIN
        SELECT @SQL = 'with cte as (SELECT  GRP , ' + fieldname + ' ,
                                            ROW_NUMBER() OVER ( PARTITION BY GRP ORDER BY COUNT(*) DESC ) AS F_RANK
                                    FROM    MyTable
                                    GROUP BY GRP , ' + fieldname + ')
                        UPDATE O
                        SET O.' + fieldname + ' = cte.' + fieldname + '
                        FROM ##Output O
                        INNER JOIN cte ON O.GRP = cte.GRP AND cte.F_Rank = 1' FROM ##fields WHERE id = @i
        EXEC sp_executesql @sql
        SET @i = @i + 1
    END


SELECT *
FROM ##Output

DROP TABLE ##fields
DROP TABLE ##Output

, :

SQL CPU = 31 = 504 = 39

SQL CPU = 0 = 6 = 1

, . , .

Answer 4

:

DECLARE @MyTable TABLE
(
    GRP varchar(10),
    A int,
    B int
)

INSERT INTO @MyTable
( GRP, A, B)
VALUES
('Cat', 1, 1),
('Cat', 2, 1),
('Cat', 3, 2),
('Cat', 3, 3),
('Dog', 5, 6),
('Dog', 5, 7),
('Dog', 6, 7);

first_value ( cte, )

SELECT DISTINCT
    GRP,
    FIRST_VALUE(A) OVER(PARTITION BY GRP ORDER BY d.A_CNT DESC) AS A_RANK,
    FIRST_VALUE(B) OVER(PARTITION BY GRP ORDER BY d.B_CNT DESC) AS B_RANK
FROM
(
    SELECT 
        GRP, 
        A, 
        ROW_NUMBER() OVER (PARTITION BY A ORDER BY GRP, A) AS A_CNT,
        B, 
        ROW_NUMBER() OVER (PARTITION BY B ORDER BY GRP, B) AS B_CNT
    FROM @MyTable
) d

:

GRP A_RANK  B_RANK
Cat 3   1
Dog 5   7