This SQL problem is the MAX() amount of frustration I can handle right now. (2 Viewers)

[KitaYama]

I'm sorry for the length of the question. I don't know how I can explain it shorter.
This is a working sql:

SELECT
    PricesOrderedFromFK,
    PricesQuantity,
    Max(PricesRegisteredOn) As RegisteredOn,
    PricesRegisteredBy
FROM
    tblProductPrices
WHERE
    PricesProductFK = 34086
Group BY
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredBy
Order BY
    PricesQuantity,
    Max(PricesRegisteredOn);

I want to add a field (PricesUnitPrice) to the query from the same record that produced Max(PricesRegisteredOn) As RegisteredOn.
I can not add PricesUnitPrice as a group, because it causes duplicates in PricesQuantity.
I think I need a sub query, but alas, that's out of my knowledge.

The data in table is :

PricePK	PricesProductFK	PricesOrderedFromFK	PricesRegisteredOn	PricesQuantity	PricesUnitPrice	PricesRegisteredBy
612	34086	159	2016/03/01	1	699.00	126
613	34086	159	2016/03/01	2	583.00	126
614	34086	159	2016/03/01	6	556.00	126
615	34086	159	2016/03/01	10	530.00	126
1743	34086	159	2016/03/01	10	625.00	126
616	34086	159	2024/11/05	21	503.00	126
1178	34086	159	2016/03/01	21	594.00	126
2417	34086	159	2025/12/20	21	802.00	126
617	34086	159	2016/03/01	51	492.00	126
619	34086	159	2023/10/17	51	581.00	126
618	34086	159	2016/03/01	101	489.00	126

Expected query result is : (This is the result of above sql + The additional field "PricesUnitPrice")
(red records in above table)

PricesOrderedFromFK	PricesQuantity	RegisteredOn	PricesRegisteredBy	PricesUnitPrice
59	1	2016/03/01	126	699.00
59	2	2016/03/01	126	583.00
59	6	2016/03/01	126	556.00
59	10	2024/11/05	126	625.00
59	21	2025/12/20	126	802.00
59	51	2023/10/17	126	581.00
59	101	2016/03/01	126	489.00

Expected result rule is:
1- No duplicates in PricesQuantity (Should be grouped and sorted ASC)
2- The latest date of PricesQuantity (in each group of rule one : PricesQuantity field).
3- The PricesUnitPrice field for the result record in rule 1 and 2.


Thanks for your time and help.

 

[KitaYama]

After several trials and errors, the following seems to work.
But honestly, I'm not sure if it can be relied on. There seems to be too many joins:

I appreciate if you can correct this one, or suggest something to be used instead.
Thanks.

SELECT
    q.PricesOrderedFromFK,
    q.PricesQuantity,
    q.RegisteredOn,
    q.PricesRegisteredBy,
    p.PricesUnitPrice
FROM
    (
        SELECT
            PricesProductFK,
            PricesOrderedFromFK,
            PricesQuantity,
            PricesRegisteredBy,
            Max(PricesRegisteredOn) AS RegisteredOn
        FROM
            tblProductPrices
        WHERE
            PricesProductFK = 34086
        GROUP BY
            PricesProductFK,
            PricesOrderedFromFK,
            PricesQuantity,
            PricesRegisteredBy
    ) AS q
    INNER JOIN tblProductPrices AS p ON p.PricesProductFK = q.PricesProductFK
    AND p.PricesOrderedFromFK = q.PricesOrderedFromFK
    AND p.PricesQuantity = q.PricesQuantity
    AND p.PricesRegisteredBy = q.PricesRegisteredBy
    AND p.PricesRegisteredOn = q.RegisteredOn
ORDER BY
    q.PricesQuantity,
    q.RegisteredOn;