sql server 2005 - Replicate Excel's mode function using SQL? -

how can replicate excel's mode function using sql?

if run mode function on set of numbers in excel return 1 mode value if there multiple mode values. need sql work in same way.

for series of numbers excel returns mode of 8. because 8 first modal number appear.

6 7 8 3 3 8 0 2 2 

if there no mode example numbers unique should return na.

this code have far.

how can replicate excel's mode function using sql? if run mode function on set of numbers in excel return 1 mode value if there multiple mode values. need sql work in same way.

this have far. deleting rows occurences=1 deal series no mode.

 --i wanted use cte mode, won't work part of union query select ric,period,inputfile,occurrences,amount #mode1 (select aa.ric,aa.period,aa.inputfile,aa.amount,count(*) occurrences temppivottable aa --where aa.ric='ustrdap' , aa.period='2006' , aa.inputfile='c:\falconingest\input\us april 2006.xls' group aa.ric,aa.period,aa.inputfile,aa.amount)  select ric,vendor,period,filedate,inputfile,yearlyorquarterly,sortableperiod,numericfiledate,maxamount #mode2 ( select t.ric,'o' vendor,t.period,filedate,t.inputfile,yearlyorquarterly,sortableperiod,numericfiledate,max(occurrences) maxamount temppivottable t inner join #mode1 on t.ric=a.ric , t.period=a.period , t.inputfile=a.inputfile group t.ric,t.period,filedate,t.inputfile,yearlyorquarterly,sortableperiod,numericfiledate )as  select ric,vendor,period,filedate,inputfile,yearlyorquarterly,sortableperiod,numericfiledate,amount,occurrences #mode3 ( select a.ric, 'o' vendor,a.period,filedate,a.inputfile,yearlyorquarterly,sortableperiod,numericfiledate,amount,occurrences #mode1 inner join #mode2 m on a.ric=m.ric , a.period=m.period , a.inputfile=m.inputfile occurrences=maxamount  )  --deal cases there no mode select ric,vendor,period,filedate,inputfile,yearlyorquarterly,sortableperiod,numericfiledate,amount #mode4  from( select   ric,'o' vendor,period,filedate,inputfile,yearlyorquarterly,sortableperiod,numericfiledate,0 amount #mode3 occurrences=1  group ric,period,filedate,inputfile,yearlyorquarterly,sortableperiod,numericfiledate having count(*)>1 ) delete #mode3 occurrences=1   select a.ric, 'o' vendor,a.period,filedate,a.inputfile,yearlyorquarterly,sortableperiod,numericfiledate,amount #mode1 inner join #mode2 m on a.ric=m.ric , a.period=m.period , a.inputfile=m.inputfile occurrences=maxamount , maxamount>1 union select * #mode4  --put series no mode na  drop table #mode1 drop table #mode2 drop table #mode3 drop table #mode4 

subsquently, i've come simplified code.

select code,inputfile,period,amount,count(*) amountcount, ranking=dense_rank() on (partition code,period,inputfile order count(*) desc) temppivottable group code,inputfile,period,amountmore of amount modal amount.   

it's ok there 1 mode value. in example below 3 , 8 mode values. there multiple mode values must choose 8 appears first in alphabetical list of vendors.

vendor amount 6 b 7 c 8 d 3 e 3 f 8 g 0 h 2 2

below statement return mode value if single, mode value of first (alphabetically) vendor if set multimodal, , return null if values unique (no mode exist).

;with m (     select *,         acnt = count(1) on (partition amount),         tcnt = count(1) on ()     tablename ) select top (1)     case         when acnt = 1 , tcnt > 1 null         else amount     end mode m order acnt desc, vendor; 

sqlfiddle sample

to find mode within (inputfile, code, period) tuple may try:

;with r1 (     select inputfile, code, period, vendor, amount,         acnt = count(1) on (partition inputfile, code, period, amount),         tcnt = count(1) on (partition inputfile, code, period)     tablename ), r2 (     select inputfile, code, period, amount, acnt, tcnt,         rn = row_number() on (partition inputfile, code, period order acnt desc, vendor)     r1 ) select inputfile, code, period,     mode = case when acnt = 1 , tcnt > 1 null else amount end r2 rn = 1 

sqlfiddle sample