First Aggregate Function Which I Can Use With Having Clause

I have a weird requirement which I need to use inside my Stored Procedure in SQL Server 2008 R2. I need a FIRST aggregate function which returns the first element of a sequence an

Solution 1:

How about

SELECT f1.CategoryName, SUM(f1.Price) 
FROM@fooTableAS f1
INNERJOIN (
    SELECT MinAllow, CategoryName
    FROM (
         SELECT MinAllow, CategoryName, ROW_NUMBER() OVER (PARTITIONBY CategoryName ORDERBY ID) AS m
         FROM@fooTable
    ) AS f
    WHERE m =1
) AS f2 ON f1.CategoryName = f2.CategoryName
WHERE f2.MinAllow >=@minAllowParamGROUPBY f1.CategoryName

I know not a very elegant query. Maybe I can tweak it a little if I work on it a little longer!

Edit: Ok the inner most subquery should be unnecessary. This should also work:

SELECT f1.CategoryName, SUM(f1.Price) 
FROM@fooTableAS f1
INNERJOIN (
    SELECT MinAllow, CategoryName, ROW_NUMBER() OVER (PARTITIONBY CategoryName ORDERBY ID) AS m
    FROM@fooTable
) AS f2 ON f1.CategoryName = f2.CategoryName
WHERE f2.m =1AND f2.MinAllow >=@minAllowParamGROUPBY f1.CategoryName

Solution 2:

UPDATE: a readable query:

SELECT ft.CategoryName, SUM(ft.Price) 
FROM fooTable ft
    cross apply
    (
       select top 1 MinAllow
         from fooTable a
        where a.CategoryName = ft.CategoryName
        orderby ID
    ) a
where a.MinAllow >=@minAllowParamGROUPBY ft.CategoryName;

You might filter categories having first (by id?) MinAllow >= @minAllowParam:

...
innerjoin 
(
   select-- Add columns you might need
     CategoryName,
     Price
   from
     fooTable
   innerjoin
   (
     -- First ID in categoryselectmin(id) id
     from
       fooTable
     groupby
       CategoryName
   ) firstID
   -- Back to all columnsON fooTable.ID = firstID.ID
   -- but only if category sequence starts properlyAND fooTable.MinAllow >=@minAllowParam
) a
-- Allow MinAllow categories onlyON fooTable.CategoryName = a.CategoryName

Solution 3:

Have a look at ROW_NUMBER() with partitioning:

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Solution 4:

There are two ways that I know to achieve FIRST aggregate function:

ROW_NUMBER()

(WHERE ROW_NUMBER_COLUMN = 1)

http://msdn.microsoft.com/en-us/library/ms186734.aspx

AND

SELECT ...., (SELECT TOP 1FROM ... WHERE (outertablejoin) ORDERBY SOMETHING) AS [FIRST]
FROM ...