DECLARE @T TABLE(BranchID INT,MostProducts VARCHAR(100))

INSERT INTO @T VALUES (1,'trainers')
INSERT INTO @T VALUES (1,'trainers')
INSERT INTO @T VALUES (1,'jumpers')
INSERT INTO @T VALUES (2,'tie')
INSERT INTO @T VALUES (2,'shoes')
INSERT INTO @T VALUES (2,'tie')
INSERT INTO @T VALUES (2,'shoes')

;WITH CTE
AS
(
    SELECT distinct
        COUNT(MostProducts) OVER(PARTITION BY BranchID,MostProducts) AS Seq,
        BranchId,
        MostProducts   
        FROM
        @T
)
, CTE1
AS
(
    SELECT *,DENSE_RANK() OVER(PARTITION BY BranchID ORDER BY CTE.Seq DESC) AS Seq1
    FROM CTE
)

SELECT
    CTE1.BranchID,
    CTE1.MostProducts,
    CTE1.Seq
FROM
    CTE1
WHERE
    CTE1.Seq1 = 1

SELECT BranchNo,Products
FROM (SELECT DISTINCT BranchNo FROM table)t
CROSS APPLY (SELECT TOP 1 WITH TIES Products
             FROM table
             WHERE BranchNo = t.BranchNo
             GROUP BY Products
             ORDER BY COUNT(*) DESC)t1

quote:

---

Originally posted by MartynOgea

Hi everyone, I need help in finding out how to display only the most frequent product for each branch.

---