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.