T-SQL Select MAX from subquery

Question

I've got the following tables in a database. We have Product which have multiple Products for a Series, and we have ProductVariation which has multiple product variations per product.

We are wishing to perform a set of aggregate queries on the ProductVariation table for a set of products based on the ID of the series. For a SeriesID of 276, ProductID's 400-415 match the SeriesID. We then want to find minimum and maximum of various fields in the ProductVariation table that have ProductID's 400-415 assigned to them.

The T-SQL statement I've written is the following:-

SELECT(
   SELECT MAX([X]) FROM [ProductVariation] AS B WHERE B.ProductID = A.ProductID      
)
FROM [Product] AS A
WHERE SeriesID = 12 AND IsDeleted = 0 and IsEnabled = 1

but this returns 15 rows of minimum and maximum data. I was looking for the Maximum of the maximum, but I don't know how to adapt the above statement to retrieve that. We will need to do 10 of these aggregates at least in the same query as well.

Can anyone suggest how to get the maximum of the maximum?

Cheers, Mike.

Oliver · Answer 1 · 2016-02-12T09:52:33.670

3

So essentially, you just want the one Maximum for the whole series? I think there are a bit too many MAX's around in the other solutions. Just use a join:

SELECT MAX(PV.X), MAX(PV.Y)
FROM [Product] AS P
JOIN [ProductVariation] AS PV ON P.ProductID = PV.ProductID
WHERE P.SeriesID = 12 AND P.IsDeleted = 0 and P.IsEnabled = 1

This way, you can also query multiple maximums at once.

edited Feb 12 '16 at 09:52

answered Feb 12 '16 at 09:21

Oliver

3,225
1
18
12

score 2 · Answer 2 · answered Feb 12 '16 at 08:54

2

If I understood you well, you are asking for this:

SELECT MAX(
   SELECT MAX([WheelDiameter]) FROM [AUT].[dbo].[Product] AS B WHERE B.ProductBlockID = A.ProductBlockID      
)
FROM [AUT].[dbo].[ProductBlock] AS A
WHERE SeriesID = 12 AND IsDeleted = 0 and IsEnabled = 1

answered Feb 12 '16 at 08:54

Shukri Gashi

535
2
10

This is what I tried too, but I get `Incorrect Syntax near the keyword SELECT` on line 2. – Mike Upjohn Feb 12 '16 at 08:57
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery." – diiN__________ Feb 12 '16 at 08:58
WITH NewSelect AS (SELECT ( SELECT MAX([WheelDiameter] ) FROM [AUT].[dbo].[Product] AS B WHERE B.ProductBlockID = A.ProductBlockID ) AS max1 FROM [AUT].[dbo].[ProductBlock] AS A WHERE SeriesID = 12 AND IsDeleted = 0 and IsEnabled = 1) SELECT MAX(max1) – Shukri Gashi Feb 12 '16 at 09:02

score 1 · Accepted Answer · answered Feb 12 '16 at 10:04

Ok, so my issue was that the above were all right, but it turns out I was looking for group by SeriesID, to return MIN and MAX of set fields per SeriesID. Many thanks for all the SQL statements, you guys have helped clear up a problem I've had many times before! :)

In sql:-

@Oliver's post was what made it trigger as to what I needed. I then added a group by for what I needed to do, to GROUP BY SeriesID.

In LINQ:-

(from x in productBlocks
    join y in products on x.ProductBlockID equals y.ProductBlockID
    where x.SeriesID == id && x.IsEnabled && !x.IsDeleted
    group y by x.SeriesID into g
    select new SeriesCharacteristicsViewModel
    {
        MinWheelDiameter = g.Min(s => s.WheelDiameter),
        MaxWheelDiameter = g.Max(s => s.WheelDiameter),
        ShoreHardness = g.Select(s => s.ShoreHardness).FirstOrDefault(),
        MinimumCarryingCapacityAt4kmh = g.Min(s => s.StaticCapacity),
        MaximumCarryingCapacityAt4kmh = g.Max(s=>s.StaticCapacity),
        MinimumRollingResistance = g.Min(s=>s.RollingResistance),
        MaximumRollingResistance = g.Max(s=>s.RollingResistance),
        MinimumTemperature = g.Min(s=>s.TempFrom),
        MaximumTemperature = g.Max(s=>s.TempTo)
    }
)

bmsqldev · Answer 4 · 2016-02-12T09:48:45.457

0

SELECT MAX(A.MAXVALUE) as MAXVALUE
FROM
(
 SELECT MAX([WheelDiameter]) MAXVALUE 
 FROM [AUT].[dbo].[Product] AS B 
 INNER JOIN FROM [AUT].[dbo].[ProductBlock] AS A 
 ON ( B.ProductBlockID = A.ProductBlockID) 
 WHERE SeriesID = 12 
 AND IsDeleted = 0 
 AND IsEnabled = 1      
)A

edited Feb 12 '16 at 09:48

answered Feb 12 '16 at 08:57

bmsqldev

2,627
10
31
65

nanestev · Answer 5 · 2016-02-12T18:52:56.200

0

Try this:

SELECT a.SeriesID, min(c.min), max(c.max)
FROM   [Product] AS A outer apply
       ( SELECT MIN(X) min, MAX([X]) max 
         FROM   [ProductVariation] AS B 
         WHERE B.ProductID = A.ProductID ) as c
WHERE  a.SeriesID in (12,13,14) AND a.IsDeleted = 0 and a.IsEnabled = 1
GROUP BY a.SeriesID

edited Feb 12 '16 at 18:52

answered Feb 12 '16 at 09:01

nanestev

822
8
15

score 0 · Answer 6 · answered Feb 12 '16 at 09:05

SELECT MAX(A.Maximum) as MAXVALUE
FROM
(
    SELECT MAX([WheelDiameter]) Maximum
    FROM [AUT].[dbo].[Product] AS Product WHERE Product.ProductBlockID = ProductBlock.ProductBlockID
    INNER JOIN [AUT].[dbo].[ProductBlock] AS ProductBlock ON Product.ProductBlockID = ProductBlock.ProductBlockID
    WHERE SeriesID = 12 AND IsDeleted = 0 and IsEnabled = 1      
) A

T-SQL Select MAX from subquery

6 Answers6