Separating the list of value by comparing frequency

Question

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!!

Are you sure output can contain duplicate values? If yes - what is the rule? — Y.B., Jul 01 '16 at 10:25
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.. — Ankur Alankar Biswal, Jul 01 '16 at 10:56
So, one time less for each? if there is ID=5 in input should it be reflected in output at all? — Y.B., Jul 01 '16 at 11:06
yes if it is present single time.. it should not display in output.. — Ankur Alankar Biswal, Jul 01 '16 at 11:10
Looks like you are searching the maximum number of consecutive values (or longest sequence) for each value. i.e. if you had `1,2,3,1,1,1,2,3,3,3,3,4,4` the output would be `1,1,1,2,3,3,3,3,4,4` ? — Ocaso Protal, Jul 01 '16 at 11:26

score 1 · Accepted Answer · edited May 23 '17 at 12:15

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

You could partition the data in the row_number function and filter out rows with number 1. It should be more efficient: Something like `numbers as ( select id, row_number() over (partition by id order by id) as n from src ) select id from numbers where n <> 1` It renders a better looking execution plan in my test. — jpw, Jul 01 '16 at 11:47

Separating the list of value by comparing frequency

1 Answers1