Solved How to solve this Top 1 Query? (1 Viewer)

[KitaYama]

I have this query :

SELECT *
FROM tblProductPrices
WHERE PricesProductFK=34055
ORDER BY PricesRegisteredOn ASC, PricesQuantity ASC

Which returns this result:

qryProductPrices

PricePK	PricesProductFK	PricesOrderedFromFK	PricesRegisteredOn	PricesQuantity	PricesUnitPrice	PricesRegisteredBy
30043​	34055​	59​	2023/09/19​	1​	$2,816.00​	11​
30044​	34055​	59​	2023/09/19​	2​	$2,347.00​	201​
30045​	34055​	59​	2023/09/19​	6​	$2,236.00​	201​
30046​	34055​	59​	2023/09/19​	10​	$2,130.00​	201​
30047​	34055​	59​	2023/09/19​	21​	$2,023.00​	33​
30048​	34055​	59​	2023/09/19​	51​	$1,982.00​	33​
30049​	34055​	59​	2023/09/19​	101​	$1,972.00​	18​
30070​	34055​	59​	2023/10/17​	21​	$2,387.00​	18​
50148​	34055​	59​	2023/12/23​	6​	$2,513.00​	96​

I need to find the Latest price for the quantity that is less AND nearest to 22.
In above case, I need the query to show PricePK -->30070 (Which is $2,387.00)
Because
1- it's the nearest quantity bellow 22
2- its date is greater than 30047

I've tested this query, but apparently it returns the last record (PricePK=50148).

SELECT
    TOP 1 *
FROM
    tblProductPrices
WHERE
    PricesProductFK=34055
    AND PricesOrderedFromFK=59
    AND PricesQuantity<=22
ORDER BY
    PricesRegisteredOn DESC, PricesQuantity DESC

How can I change this query to find the nearest quantity bellow 22 with the latest date?

thanks for any kind of advice.

Edit : Because of a lot of situation that are going to be considered, the sql will be dynamically built in vba and the result would be extracted to a text box on a form. So as far as possible, I prefer not to use saved queries.
Thanks again.

 

[June7]

Do you need this for each product?

Review https://stackoverflow.com/questions/78551767/find-product-id-in-shipment/78552498#78552498

 

[KitaYama]

Do you need this for each product?

No, only one product at a time. I need to pass PricesProductFK, PricesOrderedFromFK and PricesQuantity to a function. The function creates the sql, and a recordset out of it, then returns the Price field. (Only for one product with each call of the function)

thanks for looking into this.

 

[GaP42]

Did you try ORDERBY PricesQuantity DESC then by PricesRegisteredOn?

 

[KitaYama]

I added a new top 1 :

SELECT
    TOP 1 PricesQuantity
FROM
    tblProductPrices
WHERE
    PricesQuantity<=22
    AND PricesOrderedFromFK=59
    AND PricesProductFK=34055
ORDER BY
    PricesQuantity DESC;

This returns 21.
Then used this 21 in my previous Top 1:

SELECT
    TOP 1 *
FROM
    tblProductPrices
WHERE
    PricesProductFK=34055
    AND PricesOrderedFromFK=59
    AND PricesQuantity=21
ORDER BY
    PricesRegisteredOn DESC, PricesQuantity DESC

This one shows the correct price.


Not that convenient, but works for now.
Any better solution is much appreciated.

 

[KitaYama]

Did you try ORDERBY PricesQuantity DESC then by PricesRegisteredOn?

Yes, seems that my order by was wrong.
Changing it per your advice seems that it works.
I have to do some tests to be sure if it's all OK.

I really appreciate it. It's much better than my second thought (#5)

 

[GaP42]

Top SQL are very dependent upon the Order. Glad to help. The set of records with PricesQuantity <= 22 needed sorting by that item to get the list closest to the target of 22, then to sort by date to get the most recent of those that had the same PricesQuantity value.