SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE input
    ([foo] int, [bar] int, [id] int)
;
    
INSERT INTO input
    ([foo], [bar], [id])
VALUES
    (123, 1, 1),
    (123, 1, 2),
    (123, 2, 3),
    (456, 1, 4),
    (456, 2, 5)
;
5 rows affected

    SELECT 
        foo, 
        bar, 
        COUNT(bar) AS barCount
    FROM input it
    GROUP By foo, bar


foo
bar
barCount


123
1
2

456
1
1

123
2
1

456
2
1


with barCounter AS
(
    SELECT 
        foo, 
        bar, 
        COUNT(bar) AS barCount
    FROM input it
    GROUP By foo, bar
)
SELECT DISTINCT 
            foo, 
            FIRST_VALUE(bar) OVER (PARTITION BY foo ORDER BY barCount desc,bar DESC) AS bar, 
            max(barCount) OVER (PARTITION BY foo) AS barCount
        FROM barCounter


foo
bar
barCount


123
1
2

456
2
1


with barCounter AS
(
    SELECT 
        foo, 
        bar, 
        COUNT(bar) AS barCount
    FROM input it
    GROUP By foo, bar
)
SELECT DISTINCT 
            foo, 
            FIRST_VALUE(bar) OVER (PARTITION BY foo ORDER BY barCount desc,bar ASC) AS bar, 
            max(barCount) OVER (PARTITION BY foo) AS barCount
        FROM barCounter


foo
bar
barCount


123
1
2

456
1
1