# SolvedHow to solve this Top 1 Query? (1 Viewer)

#### KitaYama

##### Well-known member
I have this query :

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

Which returns this result:

qryProductPrices
PricePKPricesProductFKPricesOrderedFromFKPricesRegisteredOnPricesQuantityPricesUnitPricePricesRegisteredBy
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).

SQL:
``````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.

Last edited:
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.

Did you try ORDERBY PricesQuantity DESC then by PricesRegisteredOn?

I added a new top 1 :

SQL:
``````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:

SQL:
``````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.

Did you try ORDERBY PricesQuantity DESC then by PricesRegisteredOn?
Yes, seems that my order by was wrong.
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)

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.

Replies
1
Views
515
Replies
25
Views
1,564
Replies
4
Views
612
Replies
2
Views
756
Replies
9
Views
699