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

[KitaYama]

It might be worth repeating the tests with a larger dataset as that may affect the overall efficiency.
My very simple test database is attached

Million thanks for all your efforts.
I'm at home and don't have Access here. I'll check it on Monday.
I really appreciate your assist.

 

[DaveMD]

With this information that you are using SQL Server and you are looking to scale this (you note the table is expected to grow to half a million records), you have several good ways of tackling this:

1. As @DHookom notes, I also would highly suggest having this query be processed on SQL Server either as a pass-through query or using VBA to construct the query dynamically and using ADO to execute it on SQL Server. SQL Server will much better optimize performance particularly as your data size grows.

2. Use indexes on SQL Server to improve performance. If you are going to be frequently querying for particular PricesProductFK values, an index on that field could go a long way and you could also incorporate other fields into the index (as additional key fields) to optimize.

3. As @isladogs suggests, absolutely test with larger datasets. Query performance can vary significantly with larger datasets and what performs best on a small dataset can perform very differently as the dataset gets larger.

4. With SQL Server, you have many more options for how to write the query. For example, you could take a common SQL Server approach that uses the ROW_NUMBER window function to get the result you are looking for. See below for your query that uses that approach (this would need to run as a pass-through or direct query on SQL Server as this is not supported by the Access DB Engine).

WITH CTE AS (
    SELECT p.*,
           ROW_NUMBER() OVER (
               PARTITION BY p.PricesProductFK, p.PricesOrderedFromFK,
                            p.PricesQuantity, p.PricesRegisteredBy
               ORDER BY p.PricesRegisteredOn DESC, p.PricesUnitPrice DESC
           ) AS rownum
    FROM tblProductPrices AS p
)
SELECT
    PricesProductFK,
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredOn AS RegisteredOn,
    PricesRegisteredBy,
    PricesUnitPrice
FROM CTE
WHERE rownum = 1 AND PricesProductFK = 34086;

 

[Minty]

I agree with @DaveMD that a windowing function in SQL server and suitable indexes would probably speed this up further.
The windowing functions are an absolute winner for this type of query.

If you run the current query directly in SQL server, you would be able see the execution plan which will suggest suitable indexes if it sees performance improvements as a result.

 

[KitaYama]

With this information that you are using SQL Server and you are looking to scale this (you note the table is expected to grow to half a million records), you have several good ways of tackling this:

1. As @DHookom notes, I also would highly suggest having this query be processed on SQL Server either as a pass-through query or using VBA to construct the query dynamically and using ADO to execute it on SQL Server. SQL Server will much better optimize performance particularly as your data size grows.

2. Use indexes on SQL Server to improve performance. If you are going to be frequently querying for particular PricesProductFK values, an index on that field could go a long way and you could also incorporate other fields into the index (as additional key fields) to optimize.

3. As @isladogs suggests, absolutely test with larger datasets. Query performance can vary significantly with larger datasets and what performs best on a small dataset can perform very differently as the dataset gets larger.

4. With SQL Server, you have many more options for how to write the query. For example, you could take a common SQL Server approach that uses the ROW_NUMBER window function to get the result you are looking for. See below for your query that uses that approach (this would need to run as a pass-through or direct query on SQL Server as this is not supported by the Access DB Engine).

WITH CTE AS (
    SELECT p.*,
           ROW_NUMBER() OVER (
               PARTITION BY p.PricesProductFK, p.PricesOrderedFromFK,
                            p.PricesQuantity, p.PricesRegisteredBy
               ORDER BY p.PricesRegisteredOn DESC, p.PricesUnitPrice DESC
           ) AS rownum
    FROM tblProductPrices AS p
)
SELECT
    PricesProductFK,
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredOn AS RegisteredOn,
    PricesRegisteredBy,
    PricesUnitPrice
FROM CTE
WHERE rownum = 1 AND PricesProductFK = 34086;

I just wanted to come back and tell you how much better your solution is than mine. Now after a while using it, it's Indeed super fast!

I used your suggestion as a pass-through query and set it as the record source of a subform. Even though I can't use a parent-child link for a subform with a pass-through record source and have to rebuild the SQL in VBA and reassign it to the query, it's still super, super fast.
I'm really not sure how to thank you. All I can say is that you helped so much.

A million thanks!

 

[MadPiet]

If you're doing this in SQL Server, then why not use APPLY?

SELECT
    p.PricesProductFK,
    p.PricesOrderedFromFK,
    p.PricesQuantity,
    p.PricesRegisteredOn AS RegisteredOn,
    p.PricesRegisteredBy,
    p.PricesUnitPrice,
    lp.LastPrice
FROM
    tblProductPrices AS p
/* OUTER APPLY is analogous to LEFT JOIN to a table-valued function... CROSS APPLY is analogous to INNER JOIN to a table-valued function */

OUTER APPLY (
        SELECT TOP 1 p2.PricePK AS LastPrice
        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
    ) lp

Getting your head around the differences between Access SQL and Transact SQL is fundamental to getting your code to work fast. That and make friends with Indexing. (Learned that one the hard way)

 

[KitaYama]

If you're doing this in SQL Server, then why not use APPLY?

The simple answer is because it's the first time I heard it and I don't know anything about it.
But thanks for offering something new to learn.
I'll study a bit and try to understand how it works and the differences between my current solution and APPLY>

Thanks again.

 

[KitaYama]

If you're doing this in SQL Server, then why not use APPLY?

Before I start learning, I wanted to be sure this shows the correct result.
Running your suggested code (with a slightly corrections), it doesn't show the expected result.
It shows all the prices.

But I'm still reading about APPLY. For now I stick with CTE until I learn more about APPLY. Maybe I can find a way around it on my own.
Here's my final query:

SELECT
    p.PricesProductFK,
    p.PricesOrderedFromFK,
    p.PricesQuantity,
    p.PricesRegisteredOn AS RegisteredOn,
    p.PricesRegisteredBy,
    p.PricesUnitPrice,
    lp.LastPrice
FROM
    tblProductPrices AS p
OUTER APPLY (
    SELECT TOP 1 p2.PricePK AS LastPrice
    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
) lp
WHERE p.PricesProductFK =36065

 

[MadPiet]

Can you post some sample data? (It can be fake... as long as it's representative)
CREATE TABLE script(s), INSERT script(s)... well, if you want a tested result.

Is what you posted something like a price history or purchase history table? The way I solved the "get the most recent price prior to [some date]" is to use APPLY.... something like this:

/* for each date a loaf is made, what's the most recent price for each ingredient? */
SELECT    mfg.LoafKey,
        mfg.BakeDate,
        ri.IngredientID,
        ri.IngredientName,
        ri.IngredientWeightGrams,
        ri.RecipeWeight,
        ri.RecipeWeight/ri.IngredientWeightGrams AS PctOfPkg,
        lp.UnitPrice,
        lp.UnitPrice *  ri.RecipeWeight/ri.IngredientWeightGrams AS UnitCost
FROM BakeEvents mfg
INNER JOIN vwBaseIngredientRecipe ri
    ON mfg.LoafKey = ri.RecipeID
OUTER APPLY
/* retrieve the most recent unit price prior to the bake date */
    (SELECT TOP 1 UnitPrice
    FROM Purchases p
    WHERE p.IngredientID = ri.IngredientID
    AND p.PurchaseDate <= mfg.BakeDate
    ORDER BY p.PurchaseDate DESC) lp
WHERE ri.RecipeID IN (2,8);

the OUTER APPLY is retrieving the most recent price paid for a related ingredient prior to the BakeDate. If that's similar to what you're trying to do, then that pattern will do it.

APPLY basically lets you join a table (like the RecipeIngredients information) to a "derived table" or table-valued function. (like a TOP result). The weird part is that you don't use a typical JOIN statement, you include the join in the WHERE clause in the APPLY query. Note that CROSS APPLY is analogous to an INNER JOIN, and OUTER APPLY is analogous to an OUTER JOIN (think LEFT/RIGHT).

 

[KitaYama]

Can you post some sample data? (It can be fake... as long as it's representative)
CREATE TABLE script(s), INSERT script(s)... well, if you want a tested result.

My post #1 contains the sample data and how I need the result.
If you need a script, I can send it. But I just left my desk. So I won't be able until I return back to my desk. Maybe this evening.

Is what you posted something like a price history

yes. It's a list of price history of our products. When a new order comes in, we need to show the latest prices in each lot.
So in my #1 sample data, there are two prices of 51 quantity. I need to show the latest registered date. Or there are 3 records with 21 quantities. It shows that the price for 21 piece to 50 piece has been changed 3 times. But the query should show ONLY the one with latest (largest) registered date.

 

[MadPiet]

yes. It's a list of price history of our products. When a new order comes in, we need to show the latest prices in each lot.
So in my #1 sample data, there are two prices of 51 quantity. I need to show the latest registered date. Or there are 3 records with 21 quantities. It shows that the price for 21 piece to 50 piece has been changed 3 times. But the query should show ONLY the one with latest (largest) registered date.

Okay, so you have a price history (ItemID, PurchaseDate, UnitPrice) and then you have a new order (OrderID, OrderDate, ... ItemID, Quantity, etc)

So you'd do something like
SELECT rderID, rderDate, o.ItemID, o.Quantity, lp.PurchaseDate, lp.UnitPrice
FROM Orders o
CROSS APPLY (SELECT TOP 1 p.PurchaseDate, p.UnitPrice
FROM Purchases p
WHERE p.PurchaseDate < rderDate
AND p.ProductID = o.ItemID
ORDER BY p.PurchaseDate DESC) lp

The easiest way to understand the query is sort of from the bottom up. The Orders part is obvious (I hope!). What makes the CROSS APPLY work like a correlated subquery is the WHERE clause that (1) joins to the Orders table (p.ProductID = o.ItemID), and then limits it to the most recent purchase prior to the Order Date (WHERE p.PurchaseDate < rderDate). Ordering the CROSS APPLY result by OrderDate DESC means the latest related record is returned, and then you just grab the PurchaseDate and UnitPrice. If there might not be related Purchases, then you'd use OUTER APPLY instead of CROSS APPLY (as OUTER APPLY is like an outer join to another query).

Hope that clarifies things. If not, tell me where I went off the rails.

 

[KitaYama]

Okay, so you have a price history (ItemID, PurchaseDate, UnitPrice) and then you have a new order (OrderID, OrderDate, ... ItemID, Quantity, etc)

So you'd do something like
SELECT rderID, rderDate, o.ItemID, o.Quantity, lp.PurchaseDate, lp.UnitPrice
FROM Orders o
CROSS APPLY (SELECT TOP 1 p.PurchaseDate, p.UnitPrice
FROM Purchases p
WHERE p.PurchaseDate < rderDate
AND p.ProductID = o.ItemID
ORDER BY p.PurchaseDate DESC) lp

The easiest way to understand the query is sort of from the bottom up. The Orders part is obvious (I hope!). What makes the CROSS APPLY work like a correlated subquery is the WHERE clause that (1) joins to the Orders table (p.ProductID = o.ItemID), and then limits it to the most recent purchase prior to the Order Date (WHERE p.PurchaseDate < rderDate). Ordering the CROSS APPLY result by OrderDate DESC means the latest related record is returned, and then you just grab the PurchaseDate and UnitPrice. If there might not be related Purchases, then you'd use OUTER APPLY instead of CROSS APPLY (as OUTER APPLY is like an outer join to another query).

Hope that clarifies things. If not, tell me where I went off the rails.

Will read carefully and follow as soon as I'm back to my desk.
Thanks for your time and help.

 

[cheekybuddha]

Okay, so you have a price history (ItemID, PurchaseDate, UnitPrice) and then you have a new order (OrderID, OrderDate, ... ItemID, Quantity, etc)

So you'd do something like
SELECT rderID, rderDate, o.ItemID, o.Quantity, lp.PurchaseDate, lp.UnitPrice
FROM Orders o
CROSS APPLY (SELECT TOP 1 p.PurchaseDate, p.UnitPrice
FROM Purchases p
WHERE p.PurchaseDate < rderDate
AND p.ProductID = o.ItemID
ORDER BY p.PurchaseDate DESC) lp

The easiest way to understand the query is sort of from the bottom up. The Orders part is obvious (I hope!). What makes the CROSS APPLY work like a correlated subquery is the WHERE clause that (1) joins to the Orders table (p.ProductID = o.ItemID), and then limits it to the most recent purchase prior to the Order Date (WHERE p.PurchaseDate < rderDate). Ordering the CROSS APPLY result by OrderDate DESC means the latest related record is returned, and then you just grab the PurchaseDate and UnitPrice. If there might not be related Purchases, then you'd use OUTER APPLY instead of CROSS APPLY (as OUTER APPLY is like an outer join to another query).

Hope that clarifies things. If not, tell me where I went off the rails.

Don't be lazy, Piet!

Use code tags!

 

[MadPiet]

Don't be lazy, Piet!

Use code tags!

But the smiley faces in the code made me laugh!

 

[cheekybuddha]

But the smiley faces in the code made me laugh!

Me too!

 

[MadPiet]

With this information that you are using SQL Server and you are looking to scale this (you note the table is expected to grow to half a million records), you have several good ways of tackling this:

1. As @DHookom notes, I also would highly suggest having this query be processed on SQL Server either as a pass-through query or using VBA to construct the query dynamically and using ADO to execute it on SQL Server. SQL Server will much better optimize performance particularly as your data size grows.

2. Use indexes on SQL Server to improve performance. If you are going to be frequently querying for particular PricesProductFK values, an index on that field could go a long way and you could also incorporate other fields into the index (as additional key fields) to optimize.

3. As @isladogs suggests, absolutely test with larger datasets. Query performance can vary significantly with larger datasets and what performs best on a small dataset can perform very differently as the dataset gets larger.

4. With SQL Server, you have many more options for how to write the query. For example, you could take a common SQL Server approach that uses the ROW_NUMBER window function to get the result you are looking for. See below for your query that uses that approach (this would need to run as a pass-through or direct query on SQL Server as this is not supported by the Access DB Engine).

WITH CTE AS (
    SELECT p.*,
           ROW_NUMBER() OVER (
               PARTITION BY p.PricesProductFK, p.PricesOrderedFromFK,
                            p.PricesQuantity, p.PricesRegisteredBy
               ORDER BY p.PricesRegisteredOn DESC, p.PricesUnitPrice DESC
           ) AS rownum
    FROM tblProductPrices AS p
)
SELECT
    PricesProductFK,
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredOn AS RegisteredOn,
    PricesRegisteredBy,
    PricesUnitPrice
FROM CTE
WHERE rownum = 1 AND PricesProductFK = 34086;

dave,
isn't the rownum = 1 part the logical equivalent of a correlated-ish TOP 1? (Except I'm not sure how to set the ProductKey here...).

 

[MadPiet]

Okay, after relearning how to read... here's my answer:

SELECT *
FROM (
SELECT PricePK, ProductID, OrderedFromID, RegisteredDate, Quantity, UnitPrice, RegisteredBy, ROW_NUMBER() OVER (PARTITION BY ProductID, Quantity ORDER BY RegisteredDate DESC) as rn,
    MAX(RegisteredDate) OVER (PARTITION BY ProductID, Quantity) AS MostRecentDate
FROM Purchases
) x
WHERE x.rn = 1;

The row_number stuff numbers each row in the group (grouped by ProductID and Quantity)... then once that's calculated, you retrieve only the first instance (where row_number()... = 1) ... and there ya go. The row_number stuff is just numbering the records where the ProductID and Quantity match... then just takes the last one (top one after you order in descending order).

Looks like Dave was actually understanding the question... unlike the person writing this response... but the query returns the result set that you want. As written, it should work if you have other ProductIDs in your real dataset.

Pieter

 

[KitaYama]

Okay, after relearning how to read... here's my answer:

SELECT *
FROM (
SELECT PricePK, ProductID, OrderedFromID, RegisteredDate, Quantity, UnitPrice, RegisteredBy, ROW_NUMBER() OVER (PARTITION BY ProductID, Quantity ORDER BY RegisteredDate DESC) as rn,
    MAX(RegisteredDate) OVER (PARTITION BY ProductID, Quantity) AS MostRecentDate
FROM Purchases
) x
WHERE x.rn = 1;

The row_number stuff numbers each row in the group (grouped by ProductID and Quantity)... then once that's calculated, you retrieve only the first instance (where row_number()... = 1) ... and there ya go. The row_number stuff is just numbering the records where the ProductID and Quantity match... then just takes the last one (top one after you order in descending order).

Looks like Dave was actually understanding the question... unlike the person writing this response... but the query returns the result set that you want. As written, it should work if you have other ProductIDs in your real dataset.

Pieter

Thanks for all your efforts. But unfortunately I'm still away from my PC. Will test and report back as soon as I'm back.
Million thanks.

after relearning how to read...

It's not your reading problem. It's my poor English.