title: sql server Row Number with partition over in MongoDB for returning a subset of rows
body: <p>How to write below query using MongoDB-Csharp driver</p>
  
  <pre><code> SELECT SubSet.*
   FROM   ( SELECT    T.ProductName ,
                      T.Price ,
                      ROW_NUMBER() OVER ( PARTITION BY T.ProductName ORDER BY T.ProductName ) AS ProductRepeat
            FROM      myTable T
          ) SubSet
   WHERE  SubSet.ProductRepeat = 1
  </code></pre>
  
  <p>What I am trying to achieve is</p>
  
  <p><strong>Collection</strong></p>
  
  <ul>
  <li>ProductName|Price|SKU</li>
  <li>Cap|10|AB123</li>
  <li>Bag|5|ED567</li>
  <li>Cap|20|CD345</li>
  <li>Cap|5|EC123</li>
  </ul>
  
  <p>Expected results is</p>
  
  <ul>
  <li>ProductName|Price|SKU</li>
  <li>Cap|10|AB123</li>
  <li>Bag|5|ED567</li>
  </ul>
  
  <p>Here is the one attempt (please don't go with the object and fields)</p>
  
  <pre><code>public List&lt;ProductOL&gt; Search(ProductOL obj, bool topOneOnly)
          {
              List&lt;ProdutOL&gt; products = new List&lt;ProductOL&gt;();
  
              var database = MyMongoClient.Instance.OpenToRead(dbName: ConfigurationManager.AppSettings["MongoDBDefaultDB"]);
              var collection = database.GetCollection&lt;RawBsonDocument&gt;("Products");
              List&lt;IMongoQuery&gt; build = new List&lt;IMongoQuery&gt;();
  
              if (!string.IsNullOrEmpty(obj.ProductName))
              {
                  var ProductNameQuery = Query.Matches("ProductName", new BsonRegularExpression(obj.ProductName, "i"));
                  build.Add(ProductNameQuery);
              }
              if (!string.IsNullOrEmpty(obj.BrandName))
              {
                  var brandNameQuery = Query.Matches("BrandName", new BsonRegularExpression(obj.BrandName, "i"));
                  build.Add(brandNameQuery);
              }
  
              var fullQuery = Query.And(build.ToArray());
              products = collection.FindAs&lt;ProductOL&gt;(fullQuery).SetSortOrder(SortBy.Ascending("ProductName")).ToList();
              if (topOneOnly)
              {
                  var tmpProducts = new List&lt;ProductOL&gt;();
                  foreach (var item in products)
                  {
                      if (tmpProducts.Any(x =&gt; x.ProductName== item.ProductName)) { }
                      else
                          tmpProducts.Add(item);
                  }
                  products = tmpProducts;
              }
              return products;
          }
  </code></pre>