Alok Kumar Alok Kumar - 5 months ago 9
SQL Question

Separating the list of value by comparing frequency

I have the bellow data set output should come like less then one frequency..
i am new to sql so don't have much idea..

in the input i have 3 times 1, 2 times 2, 3 times 3 and 2 times 4. output i want 2 times 1, 1 time 2, 2 times 3 and 1 time 4..

Any suggestion how to achieve this output!!

enter image description here

Answer

This can be written in a more compact form, but just for clarity:

With Src As (        --< Source table
    Select * From (Values (1),(2),(3),(1),(1),(2),(3),(3),(4),(4),(5)) V (Id)
), Numbers As (      --< Auxiliary table with numbers from 1 to maximum row count of Src
    Select ROW_NUMBER() Over (Order By Id) As N From Src
), Counted As (      --< Calculate current number of ID occurances
    Select Id, Count(Id) As Cnt From Src Group By Id
)
    Select Id
    From Counted                 --< From distinct list of IDs
    Inner Join Numbers           --< replicate each row
    On Numbers.N < Counted.Cnt   --< one less time than the Cnt

Expression to replicate the row taken from SQL: Repeat a result row multiple times...

jpw implementation (please feel free to copy it into your own answer):

With Src As (                                --< Source table
    Select * From (Values (1),(2),(3),(1),(1),(2),(3),(3),(4),(4),(5)) V (Id)
), Numbered As (                             --< Number ID occurances
    Select Id, row_number() Over (Partition By id Order By id) As n From Src
)
    Select Id From Numbered Where n > 1      --< Take one off