Alternative to ORDER BY GROUP BY to get first matches per ForeignKey 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.



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