sort items based on their appears count

Question

Hello Experts,

I have data like this

d b c

a d

c b

a b

c a

c a d

c

if you analyse, you will find the appearance of each element as follows

a: 4 b: 3 c: 5 d: 2

According to appearance my sorted elements would be c,a,b,d

and final output should be

c b d

a d

c b

a b

c a

c a d

c

Any clue, how we can achieve this using sql query ?

Answer 1

Unless there is another column which dictates the order of the input rows, it will not be possible to guarantee that the output rows are returned in the same order. I've made an assumption here to order them by the three column values so that the result is deterministic.

It's likely to be possible to compact this code into fewer steps, but shows the steps reasonably clearly.

Note that for a large dataset, it may be more efficient to partition some of these steps into SELECT INTO operations creating temporary tables or work tables.

DECLARE @t TABLE
(col1 CHAR(1)
,col2 CHAR(1)
,col3 CHAR(1)
)

INSERT @t
      SELECT 'd','b','c'
UNION SELECT 'a','d',NULL
UNION SELECT 'c','b',NULL
UNION SELECT 'a','b',NULL
UNION SELECT 'c','a',NULL
UNION SELECT 'c','a','d'
UNION SELECT 'c',NULL,NULL


;WITH freqCTE
AS
(
        SELECT col1 FROM @t WHERE col1 IS NOT NULL

        UNION ALL

        SELECT col2 FROM @t WHERE col2 IS NOT NULL

        UNION ALL

        SELECT col3 FROM @t WHERE col3 IS NOT NULL        
)
,grpCTE
AS
(
        SELECT  col1    AS val
                ,COUNT(1) AS cnt
        FROM freqCTE
        GROUP BY col1

)
,rowNCTE
AS
(
                 SELECT *
                        ,ROW_NUMBER() OVER (ORDER BY col1
                                                     ,col2
                                                     ,col3
                                           ) AS rowN
                 FROM @t
)
,buildCTE
AS
(
        SELECT rowN
               ,val
               ,cnt
               ,ROW_NUMBER() OVER (PARTITION BY rowN
                                   ORDER BY     ISNULL(cnt,-1) DESC
                                               ,ISNULL(val,'z') 
                                   ) AS colOrd
        FROM (                           
                SELECT *
                FROM            rowNCTE  AS t
                JOIN            grpCTE   AS g1
                ON              g1.val = t.col1

                UNION ALL

                SELECT *
                FROM            rowNCTE  AS t
                LEFT JOIN       grpCTE   AS g2
                ON              g2.val = t.col2

                UNION ALL

                SELECT *
                FROM            rowNCTE  AS t
                LEFT JOIN       grpCTE   AS g3
                ON              g3.val = t.col3
            ) AS x
)
SELECT b1.val  AS col1
       ,b2.val AS col2
       ,b3.val AS col3
FROM buildCTE AS b1
JOIN buildCTE AS b2
ON   b2.rowN   = b1.rowN
AND  b2.colOrd = 2
JOIN buildCTE AS b3
ON   b3.rowN   = b1.rowN
AND  b3.colOrd = 3
WHERE b1.colOrd = 1
ORDER BY b1.rowN