Select maximum value from a different table as a column-CodePudding

There are 2 tables. Table 1 is callsed Purchases, it has columns like Id, CustomerName. Data is like:

Id, CustomerName
1 , Customer1
2 , Customer2
3 , Customer3

There is also a second table called Items with columns Id, PurchaseId, ParameterName, ParameterValue. Data looks like:

Id, PurchaseId, ParameterName, ParameterValue
1 , 1         , 'Price'      , 100
2 , 1         , 'PercentOff' , 9
3 , 2         , 'Price'      , 200
4 , 2         , 'Price'      , 300
5 , 2         , 'PercentOff' , 4
6 , 2         , 'PercentOff' , 3 
7 , 3         , 'Price'      , 400

As you can see, each Purchase can have multiple custom parameters connected to it. For example, Purchase with id 2 has 2 Price parameters and 2 PercentOff parameters. Purchase with id 3 has only one parameter called Price.

I am trying to select the biggest Price and the biggest PercentOff for each purchase. This is how my code looks like right now:

SELECT
    p.Id,
    p.CustomerName,
    (SELECT 
        MAX(ParameterValue)
     FROM Items
     WHERE
        p.Id = PurchaseId 
        AND ParameterName = 'Price')
    AS BiggestPrice,
    (SELECT 
        MAX(ParameterValue)
     FROM Items
     WHERE
        p.Id = PurchaseId 
        AND ParameterName = 'PercentOff')
    AS BiggestPercentOff
FROM Purchases p

This one works fine, but it's slow as hell. So, how do I get rid of subselects and speed it up a bit?

Note that some of the purchases may have no Price whatsoever. In this case, I need to display NULL.

I expect to get data like this:

Id, CustomerName, BiggestPrice, BiggestPercentOff
1 , Customer1   , 100         , 9
2 , Customer2   , 300         , 4
3 , Customer3   , 400         , NULL

CodePudding user response：

Try doing this as a join eg

SELECT
    p.Id,
    p.CustomerName,
    MAX(CASE WHEN ParameterName = 'Price' THEN ParameterValue END) AS BiggestPrice,
    MAX(CASE WHEN ParameterName = 'PercentOff' THEN ParameterValue END) AS BiggestPercentOff
FROM Purchases p
LEFT JOIN Items i ON i.PurchaseId = p.Id
GROUP BY 
    p.Id,
    p.CustomerName

Id	CustomerName	BiggestPrice	BiggestPercentOff
1	Customer1	100	9
2	Customer2	300	4
3	Customer3	400	null