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

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

 

[DaveMD]

A subquery with TOP 1 can work well for your scenario. See example below.

SELECT
    p.PricesProductFK,
    p.PricesOrderedFromFK,
    p.PricesQuantity,
    p.PricesRegisteredOn AS RegisteredOn,
    p.PricesRegisteredBy,
    p.PricesUnitPrice
FROM
    tblProductPrices AS p
WHERE
    p.PricePK IN (
        SELECT TOP 1 p2.PricePK
        FROM tblProductPrices AS p2
        WHERE
            p2.PricesProductFK = p.PricesProductFK
            AND p2.PricesOrderedFromFK = p.PricesOrderedFromFK
            AND p2.PricesQuantity = p.PricesQuantity
            AND p2.PricesRegisteredBy = p.PricesRegisteredBy
        ORDER BY  p2.PricesRegisteredOn DESC, p2.PricesUnitPrice DESC
    )
    AND p.PricesProductFK = 34086;

 

[isladogs]

The subquery in post #2 was incorrect. It gives two records for PricesQuantity = 10 & a total of 8 records
The version suggested by @DaveMD is both much simpler and appears to be correct with only 7 records

 

[KitaYama]

The subquery in post #2 was incorrect. It gives two records for PricesQuantity = 10 & a total of 8 records
The version suggested by @DaveMD is both much simpler and appears to be correct with only 7 records

The result data shown in my first post, was a copy and paste from the data sheet view of sql from #2. It shows only 7 records and it seems the result is correct.
But I may be wrong though. I did lot of testing and may have pasted the wrong sql.
I'm away from my PC and will try to start a remote control to :
Recheck the #2 sql and @Dave E's suggestion.

Thanks to both for your help.
I really appreciate it.

 

[KitaYama]

The subquery in post #2 was incorrect. It gives two records for PricesQuantity = 10 & a total of 8 records
The version suggested by @DaveMD is both much simpler and appears to be correct with only 7 records

I just rechecked.
The sql of #2 seems to show the correct result.
Thanks.

 

[KitaYama]

A subquery with TOP 1 can work well for your scenario. See example below.

SELECT
    p.PricesProductFK,
    p.PricesOrderedFromFK,
    p.PricesQuantity,
    p.PricesRegisteredOn AS RegisteredOn,
    p.PricesRegisteredBy,
    p.PricesUnitPrice
FROM
    tblProductPrices AS p
WHERE
    p.PricePK IN (
        SELECT TOP 1 p2.PricePK
        FROM tblProductPrices AS p2
        WHERE
            p2.PricesProductFK = p.PricesProductFK
            AND p2.PricesOrderedFromFK = p.PricesOrderedFromFK
            AND p2.PricesQuantity = p.PricesQuantity
            AND p2.PricesRegisteredBy = p.PricesRegisteredBy
        ORDER BY  p2.PricesRegisteredOn DESC, p2.PricesUnitPrice DESC
    )
    AND p.PricesProductFK = 34086;

I just tested your version. The result is correct, But it seems yo be very slower than the joined sql in #2.
The #2 sql is instantaneous, but running your sql, takes around 1 to 2 seconds. First there's a blink then the result shows.

If I chnge the where clause to
AND p.PricesProductFK IN (34086, 34087, 34089, 34090, 34010);
it takes much longer time.
BE is sql server on a active directory. Table is around 10K, but is expected to grow to half a million records.

Thanks for your time.