Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jdblack
Starting Member
11 Posts |
 Posted - 2011-01-05 : 12:30:58
|
| How would you guys go about making a mode function? Custom CLR aggregate? I'm making one in C# right now. Is there a way to ensure values are ordered by SQL before being passed into the aggregate function? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 12:35:38
|
| mode as in modal average?I would probably use a ranking function.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jdblack
Starting Member
11 Posts |
Posted - 2011-01-05 : 12:37:53
|
| Yep, modal average. I'm trying to come up with a super efficient method because the aggregate will be ranking datasets every couple minutes, each with several million records minimum. |
|
|
|
jdblack
Starting Member
11 Posts |
Posted - 2011-01-05 : 12:44:05
|
| Is there a way to ensure SQL sorts the value before ordering them? Or, is there a way to iterate through a custom aggregate more than once (so that I can sort before aggregating)? I'm not very familiar with custom aggregates & the examples I've found online assume one iteration through all values. |
|
|
|
jdblack
Starting Member
11 Posts |
Posted - 2011-01-05 : 12:45:38
|
| Never mind, I suppose I can sort it while it is looping through the accumulations, then find the largest occurrence(s) during terminate. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 12:47:52
|
| I would say that you need to persist the counts and index them. It then becomes a simple select top 1 order by count desc.Another optionselect top 1 *from(select fld, rnk=count(*)from tblgroup by fld) aorder by rnk descor maybeselect top 1 fld, rnk=count(*)group by fldorder by rnk desc==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Most efficient mode