c# - SQLCLR custom aggregate with multiple parameters -

i have trouble understanding of how clr user-defined aggregates work.

i have create custom clr aggregates multiple parameters. point value of first parameter depending on second.

for example, have following values in table, , need oldest employee name each type:

    type   |   name   |   age    -------------------------------- manager    | emp 1    |   35     manager    | emp 2    |   42     developer  | emp 3    |   36     developer  | emp 4    |   45     developer  | emp 5    |   22     

so write query result using assembly:

select      type, dbo.foldestemployee(name, age) [name]        xxx group    type 

this respond :

    type   |   name    ---------------------- manager    | emp 2      developer  | emp 4     

it it's possible clr user-defined aggregate, have difficulty finding concrete example of kind of implementation.

for moment have this. create class collect datas, how can sort (or other thing) them?

using system; using system.collections.generic; using system.data; using system.data.sqlclient; using system.data.sqltypes; using microsoft.sqlserver.server; using system.text; using system.collections; using system.io;  [serializable] [sqluserdefinedaggregate(     format.userdefined,     isinvarianttoorder = false, // order changes result     isinvarianttonulls = false, // nulls change result     isinvarianttoduplicates = false, // duplicates change result     maxbytesize = -1)] public struct solder {     private list<mydata> _datas;      public void init()     {         _datas = new list<mydata>();     }      public void accumulate(sqlstring valuefield, sqlint32 valueint)     {         if (!valuefield.isnull && !valueint.isnull)         {             _datas.add(new mydata             {                 valfield = valuefield.value,                 valint = valueint.value             });         }     }      public void merge (solder group)     {         _datas.addrange(group._datas);     }      public sqlstring terminate ()     {         //...     }      public class mydata     {         public string valfield { get; set; }         public int32 valint { get; set; }     } } 

any ideas ?

there's no need store list of records - need store details of oldest record you've seen far.

something should work:

[serializable] [sqluserdefinedaggregate(     format.userdefined,     isinvarianttoorder = true,     isinvarianttonulls = true,     isinvarianttoduplicates = true,     maxbytesize = -1)] public struct solder : ibinaryserialize {     private struct mydata     {         public string name { get; set; }         public int? age { get; set; }          public int compareto(mydata other)         {             if (age == null) return other.age == null ? 0 : -1;             if (other.age == null) return 1;             return age.value.compareto(other.age.value);         }          public static bool operator <(mydata left, mydata right)         {             return left.compareto(right) == -1;         }          public static bool operator >(mydata left, mydata right)         {             return left.compareto(right) == 1;         }     }      private mydata _eldestperson;      public void init()     {         _eldestperson = default(mydata);     }      public void accumulate(sqlstring name, sqlint32 age)     {         if (!name.isnull && !age.isnull)         {             var currentperson = new mydata             {                 name = name.value,                 age = age.value             };              if (currentperson > _eldestperson)             {                 _eldestperson = currentperson;             }         }     }      public void merge (solder other)     {         if (other._eldestperson > _eldestperson)         {             _eldestperson = other._eldestperson;         }     }      public sqlstring terminate()     {         return _eldestperson.name;     }      public void write(binarywriter writer)     {         if (_eldestperson.age.hasvalue)         {             writer.write(true);             writer.write(_eldestperson.age.value);             writer.write(_eldestperson.name);         }         else         {             writer.write(false);         }     }      public void read(binaryreader reader)     {         if (reader.readboolean())         {             _eldestperson.age = reader.readint32();             _eldestperson.name = reader.readstring();         }         else         {             _eldestperson = default(mydata);         }     } }