Geek asks and answers

FIRST aggregate function that I can use with a HAVING clause

I have a strange requirement that I need to use inside my stored procedure in SQL Server 2008 R2.

I need a FIRST aggregate function that returns the first element of a sequence, and I will use this with a sentence HAVING.

Let me give an example:

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', 2, 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);

DECLARE @minAllowParam AS INT = 3;

SELECT ft.CategoryName, SUM(ft.Price) FROM @fooTable ft
GROUP BY ft.CategoryName;

As you can see, we have a table and some dummy values. Inside the query, SELECTwe group the categories together and summarize the product prices up.

This query returns the following result:

CategoryName     TotalPrice
---------------- ----------------
Cat1              345.52
Cat2              12.34
Cat3              25.43

I need something like this here:

SELECT ft.CategoryName, SUM(ft.Price) FROM @fooTable ft
GROUP BY ft.CategoryName
HAVING GetFIRST(MinAllow) >= @minAllowParam;

In our case, with a query like this, we should be able to select the following results:

INSERT INTO @fooTable  VALUES(2, 'Cat2', 'Product2', 4, 12.34);
INSERT INTO @fooTable  VALUES(4, 'Cat3', 'Product4', 4, 12.43);
INSERT INTO @fooTable  VALUES(5, 'Cat3', 'Product5', 1, 13.00);

INSERT INTO @fooTable VALUES(1, 'Cat1', 'Product1', 2, 112.2); 2 MinAllow, Cat1 . , INSERT INTO @fooTable VALUES(5, 'Cat3', 'Product5', 1, 13.00); 1 MinAllow, . , Cat3 .

: MIN MAX , !

, , , , .

?

Edit:

, , , CLR User-Defined Aggregate Function, ,

+3
4

SELECT f1.CategoryName, SUM(f1.Price) 
FROM @fooTable AS f1
INNER JOIN (
    SELECT MinAllow, CategoryName
    FROM (
         SELECT MinAllow, CategoryName, ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY ID) AS m
         FROM @fooTable
    ) AS f
    WHERE m = 1
) AS f2 ON f1.CategoryName = f2.CategoryName
WHERE f2.MinAllow >= @minAllowParam
GROUP BY f1.CategoryName

. , , !

: Ok . :

SELECT f1.CategoryName, SUM(f1.Price) 
FROM @fooTable AS f1
INNER JOIN (
    SELECT MinAllow, CategoryName, ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY ID) AS m
    FROM @fooTable
) AS f2 ON f1.CategoryName = f2.CategoryName
WHERE f2.m = 1 AND f2.MinAllow >= @minAllowParam
GROUP BY f1.CategoryName
+3

UPDATE: :

SELECT ft.CategoryName, SUM(ft.Price) 
FROM fooTable ft
    cross apply
    (
       select top 1 MinAllow
         from fooTable a
        where a.CategoryName = ft.CategoryName
        order by ID
    ) a
where a.MinAllow >= @minAllowParam
GROUP BY ft.CategoryName;

( id?) MinAllow >= @minAllowParam:

...
inner join 
(
   select 
   -- Add columns you might need
     CategoryName,
     Price
   from
     fooTable
   inner join
   (
     -- First ID in category
     select
       min(id) id
     from
       fooTable
     group by
       CategoryName
   ) firstID
   -- Back to all columns
     ON fooTable.ID = firstID.ID
   -- but only if category sequence starts properly
    AND fooTable.MinAllow >= @minAllowParam
) a
-- Allow MinAllow categories only
  ON fooTable.CategoryName = a.CategoryName
+2

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

ROW_NUMBER()

(WHERE ROW_NUMBER_COLUMN = 1)

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

and

SELECT ...., (SELECT TOP 1 FROM ... WHERE (outer table join) ORDER BY SOMETHING) AS [FIRST]
FROM ...
0
source

More articles:


All Articles