CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlaggregate-functions
Mike Upjohn
Mike Upjohn

Reputation: 1297

T-SQL Select MAX from subquery

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.

Upvotes: 2

Views: 2174

Answers (6)

nanestev
nanestev

Reputation: 872

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

Upvotes: 0

Mike Upjohn
Mike Upjohn

Reputation: 1297

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)
    }
)

Upvotes: 1

Oliver
Oliver

Reputation: 3255

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.

Upvotes: 3

bmsqldev
bmsqldev

Reputation: 2735

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

Upvotes: 0

diiN__________
diiN__________

Reputation: 7656

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

Upvotes: 0

Shukri Gashi
Shukri Gashi

Reputation: 535

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

Upvotes: 2

Related Questions