0

Since there is so much talk and articles about this online. I simply don't seem to have enough SQL knowledge to solve this seemingly simple problem.

I have a table with layered data for a Price per ProductModelID/ProductVariantID (BundledProductID isn't important in this situation). I want to get the Highest and Lowest Price (in the figure below the lowest) per ProductModelID.

Obviously knowing that GROUP BY will just grab first hits (likely based on the BTREE?) so it would get ID's 1,4 and 6 instead of the blue marked ones I want.

Is there any sensible way of achieving this and still profiting from indexes (e.a. not suffering greaty on performance on temp tables and such)?

I'm running 10.2.13-MariaDB Homebrew.

Sequel Pro

The query:

-- SELECT * FROM (
    SELECT * FROM DomainProductPrice
    WHERE DomainID = 1 #AND ProductVariantID IS NULL
    ORDER BY ProductModelID, Price
-- ) a GROUP BY ProductModelID

Table creation:

CREATE TABLE `DomainProductPrice` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`DomainID` int(11) DEFAULT NULL,
`BundledProductID` int(11) DEFAULT NULL,
`ProductModelID` int(11) DEFAULT NULL,
`ProductVariantID` int(11) DEFAULT NULL,
`PriceGroupID` int(11) DEFAULT NULL,
`TaxGroupID` int(11) DEFAULT NULL,
`Currency` int(11) DEFAULT NULL,
`Price` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table test data:

INSERT INTO `DomainProductPrice` (`id`, `DomainID`, `BundledProductID`, `ProductModelID`, `ProductVariantID`, `PriceGroupID`, `TaxGroupID`, `Currency`, `Price`)
VALUES
    (1, 1, NULL, 1, NULL, 1, 1, 1, 24),
    (2, 1, NULL, 1, 3, 1, 1, 1, 23.5),
    (3, 1, NULL, 1, 4, 1, 1, 1, 23.75),
    (4, 1, NULL, 2, NULL, 1, 1, 1, 68),
    (5, 1, NULL, 2, 5, 1, 1, 1, 66.25),
    (6, 1, NULL, 3, 9, 1, 1, 1, 101),
    (7, 1, NULL, 3, 10, 1, 1, 1, 99.5),
    (8, 1, NULL, 1, 2, 1, 1, 1, 25);

Update 1:

This would be a solution, but it's probably super bad with tons/milions of entries?

SELECT
    p.ProductModelID, p.PriceGroupID, TaxGroupID, Currency,
    (SELECT Price FROM DomainProductPrice WHERE DomainID = 1 AND ProductModelID = p.ProductModelID ORDER BY Price DESC LIMIT 1) AS Highest,
    (SELECT Price FROM DomainProductPrice WHERE DomainID = 1 AND ProductModelID = p.ProductModelID ORDER BY Price LIMIT 1) AS Lowest
FROM DomainProductPrice p
WHERE p.DomainID = 1
GROUP BY ProductModelID
  • Umm, are you acquainted with aggregate functions like MIN() and MAX()? Why not use those? – RDFozz Feb 22 '18 at 21:54
2

In MariaDB, you can use a window (ranking) function like ROW_NUMBER() or RANK():

with di as
  ( select *,  
           rank() over (partition by ProductModelID
                        order by Price)
             as rnk
    from DomainProductPrice as d
    where DomainID = 1
  )
select *
from di
where rnk = 1 ; 

Tested at dbfiddle.uk

| improve this answer | |
  • Thank your for your answer! As both Lennarts' and yours are very informative and helpful I posted a longer comment on his answer directed at you both :) – User2910293 Feb 23 '18 at 6:21
1

A classical approach is to determine max and min, and do a join to get the remaining attributes:

SELECT p.ProductModelID, p.PriceGroupID, p.TaxGroupID, p.Currency
     , q.min_price, q.max_price
FROM DomainProductPrice p 
JOIN (
    SELECT ProductModelID, min(price) as min_price, max(price) as max_price
    FROM DomainProductPrice 
    WHERE DomainID = 1
    GROUP BY ProductModelID
) as q
    ON p.ProductModelID = q.ProductModelID
   AND p.Price in (q.min_price, q.max_price)   
WHERE p.DomainID = 1

This requires 2 accesses of the table, so it is reasonable to assume that it is more efficient than the solution that requires 3 accesses (but you will have to look at the plan to be sure).

As ypercubeᵀᴹ points out, you can also use window functions in newer versions of MariaDB. With such functions, you can cope with only one access to the table:

select p.ProductModelID, p.PriceGroupID, p.TaxGroupID, p.Currency,  
       min(p.price) over (partition by p.ProductModelID) as min_price,
       max(p.price) over (partition by p.ProductModelID) as max_price
from DomainProductPrice as p
where DomainID = 1

Look at the plan to see if this is more efficient than the other two.

| improve this answer | |
  • 1
    The ON condition should use the prices, too. – ypercubeᵀᴹ Feb 22 '18 at 22:36
  • Thanks for (both your) answers! Tonight I will be able to do more rigorous testing. But so far (using 100k random entries in the table) doing yours, ypercube's and my last query on LIMIT 50. It results in 19,6ms for 2 accesses, 35,9ms for the ranking variant and 1,4ms for my 3 accesses. I use 2 indexes tho; (DomainID,ProductModelID,Price) and (ProductModelID,Price) (separate in favour of the the BTREE logic). Maybe I can test again if your queries need a different INDEX setup? Especially with the window variation I'm not sure. – User2910293 Feb 23 '18 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.