title: CLR User Defined Aggregate Function in order to get the first element of a sequence
body: <p>I need a FIRST aggregate function which returns the first element of a sequence and I will use that with HAVING clause.</p>
  
  <p>this is a question which can be viewed as a continuation of my previous question: <a href="https://stackoverflow.com/questions/10120145/first-aggregate-function-which-i-can-use-with-having-clause">FIRST aggregate function which I can use with HAVING clause</a>.</p>
  
  <p>It turns out that only an aggregate function can solve my problem so I tried to create one:</p>
  
  <pre><code>[Serializable]
  [SqlUserDefinedAggregate(
      Format.UserDefined, //use clr serialization to serialize the intermediate result
      IsInvariantToNulls = true, //optimizer property
      IsInvariantToDuplicates = false, //optimizer property
      IsInvariantToOrder = false, //optimizer property
      MaxByteSize = 8000) //maximum size in bytes of persisted value
  ]
  public struct GetFirst : IBinarySerialize {
  
      private string allValues;
  
      public void Init() {
          allValues = string.Empty;
      }
  
      private void incrementAndAdd(SqlInt32 value) {
  
          allValues += (value.Value.ToString() + ",");
      }
  
      public void Accumulate(SqlInt32 Value) {
  
          incrementAndAdd(Value);
      }
  
      public void Merge(GetFirst Group) {
  
      }
  
      public SqlInt32 Terminate() {
          // Put your code here
          return new SqlInt32(int.Parse(allValues.Split(',')[0]));
      }
  
      // This is a place-holder member field
      private SqlInt32 var1;
  
  
      public void Read(System.IO.BinaryReader r) {
  
          allValues = r.ReadString();
      }
  
      public void Write(System.IO.BinaryWriter w) {
  
          w.Write(this.allValues);
      }
  }
  </code></pre>
  
  <p>And this is how I used it:</p>
  
  <pre><code>DECLARE @fooTable AS TABLE(
      ID INT,
      CategoryName NVARCHAR(100),
      Name NVARCHAR(100),
      MinAllow INT,
      Price DECIMAL(18,2)
  );
  
  INSERT INTO @fooTable  VALUES(1, 'Cat1', 'Product1', 3, 112.2);
  INSERT INTO @fooTable  VALUES(2, 'Cat2', 'Product2', 4, 12.34);
  INSERT INTO @fooTable  VALUES(3, 'Cat1', 'Product3', 5, 233.32);
  INSERT INTO @fooTable  VALUES(4, 'Cat3', 'Product4', 4, 12.43);
  INSERT INTO @fooTable  VALUES(5, 'Cat3', 'Product5', 1, 13.00);
  INSERT INTO @fooTable  VALUES(7, 'Cat4', 'Product7', 1, 15.00);
  INSERT INTO @fooTable  VALUES(6, 'Cat4', 'Product6', 3, 13.00);
  
  DECLARE @minAllowParam AS INT = 3;
  
  SELECT ft.CategoryName, SUM(ft.Price), dbo.GetFirst(ft.MinAllow) FROM @fooTable ft
  GROUP BY ft.CategoryName
  HAVING dbo.GetFirst(ft.MinAllow) &gt;= @minAllowParam;
  </code></pre>
  
  <p>It sometimes returns the proper result, sometimes not and I am quite not sure if I implemented it right. Any thoughts if I got this right in view of my requirements?</p>