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

K

KitaYama

Well-known member
Local time
Tomorrow, 01:47
Joined
Jan 6, 2022
Messages
2,288
I'm sorry for the length of the question. I don't know how I can explain it shorter.
This is a working sql:
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 :

PricePKPricesProductFKPricesOrderedFromFKPricesRegisteredOnPricesQuantityPricesUnitPricePricesRegisteredBy
612340861592016/03/011699.00126
613340861592016/03/012583.00126
614340861592016/03/016556.00126
615340861592016/03/0110530.00126
1743340861592024/11/0510625.00126
616340861592024/11/0521503.00126
1178340861592016/03/0121594.00126
2417340861592025/12/2021802.00126
617340861592016/03/0151492.00126
619340861592023/10/1751581.00126
618340861592016/03/01101489.00126

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

PricesOrderedFromFKPricesQuantityRegisteredOnPricesRegisteredByPricesUnitPrice
5912016/03/01126699.00
5922016/03/01126583.00
5962016/03/01126556.00
59102024/11/05126625.00
59212025/12/20126802.00
59512023/10/17126581.00
591012016/03/01126489.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.
 
Last edited:
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.

SQL: 
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;
 
A subquery with TOP 1 can work well for your scenario. See example below.

SQL: 
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;
 
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
 
isladogs said:
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
Click to expand...
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.
 
isladogs said:
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
Click to expand...
I just rechecked.
The sql of #2 seems to show the correct result.
Thanks.
 
DaveMD said:
A subquery with TOP 1 can work well for your scenario. See example below.

SQL: 
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;
Click to expand...
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 longer.
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.
 
Last edited:
In my experience (especially with large tables) the joins will be more efficient than the In() clause.
If you look at the query plans you will probably see a difference in how it is gathering the results.
 
Since the BE is SQL Server, you should consider using a solution that puts the work in SQL Server like a pass-through.
 
I agree that the original solution in post #2 is faster.
What I was saying was that using your data, the 2 solutions give different results
The post #2 solution has 2 records for PricesQuantity = 10 though at the moment I'm not sure why
Is that really what you are expecting? If so, that's fine.

1766237014336.png
 
DHookom said:
Since the BE is SQL Server, you should consider using a solution that puts the work in SQL Server like a pass-through.
Click to expand...
I had thought of that, but since the query was simple, there can't be any difference. I have far more complicated queies running on very large tables and the peformance is really fast.
I'll try a passthrough with given top 1 solution to see how the result would be. But it won't be until Monday.
Thanks for your help.
 
Can you not use last (with a sort)?
Code: 
SELECT
    PricesOrderedFromFK,
    PricesQuantity,
    Max(PricesRegisteredOn) AS RegisteredOn,
    PricesRegisteredBy,
    Last(PricesUnitPrice) AS LastOfPricesUnitPrice
FROM
    tblProductPrices
WHERE
    PricesProductFK=34086
GROUP BY
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredBy
ORDER BY
    PricesQuantity,
    Max(PricesRegisteredOn),
    Last(PricesUnitPrice);

This generates the same as your desired output, with the exception of the 4th row where you have 2024/11/05 and I generate 2016/03/01. I suspect your example is wrong?

For some reason, can't paste the result - see a couple of posts down
 
Last edited:
isladogs said:
The post #2 solution has 2 records for PricesQuantity = 10 though at the moment I'm not sure why
Is that really what you are expecting? If so, that's fine.
Click to expand...
I need only one record for each quantity.

Edited post :
I had pasted the wrong date for quantity 10.
That was why you were receiving 2 records for 10.

@CJ_London found the problem in #1.
Sorry for the confusion, but the forum doesn't allow to paste a table.

Thanks again for your help.
 
Last edited:
CJ_London said:
This generates the same as your desired output, with the exception of the 4th row where you have 2024/11/05 and I generate 2016/03/01. I suspect your example is wrong?
Click to expand...
Good catch. Thanks. Yes, you are correct. The date I showed in #1 for 10 was wrong. I corrected it.
I wasn't able to paste the table. It kept giving me ooops error and asked me to reduce my post to less than 10,000 characters. That was why I had to insert a table, and copy paste each field for the entire table. I had pasted the wrong date for 10 quantity.

Back to your solution:
Your suggestion works, and gives the correct result.
I'll do some more test and will be back.

Thanks again.
 
I used your data from post #1 where both records where PricesQuantity=10 had the same date 2016/03/01.
In your post #13, the two records have different dates with record 1743 being 2024/11/05

It would have been easier both for you and us if you had uploaded a sample database with the data and query.
 
isladogs said:
It would have been easier both for you and us if you had uploaded a sample database with the data and query.
Click to expand...
You may have missed my edited reply. @CJ_London found the problem and I corrected #1.
Access makes it hard to share databases when different locals are set in control panel, or when a database is written in an Asian language.
I have had this problem frequently when others can not open my databases. Of course mostly it happens for forms, but at least twice I've been told no-one can open the database I've sent them.

I tried to paste the table, but forum software didn't like it and kept repeating oooops,something went wrong.

I'm really sorry for the confusion and appreciate the time you put on this.
Thanks.
 
My last reply was posted within a few seconds of yours. With that data change I get the correct output for all 3 queries
For info, the original query is still the fastest.
Here are the times running each query 10 times on a loop

1766242324028.png
 
Last edited:
isladogs said:
My last reply was posted within a few seconds of yours. With that data change I get the correct output for all 3 queries
For info, the original query is still the fastest.
Here are the times running each query 10 times on a loop

View attachment 122689
Click to expand...
I really appreciate this test. Since the table will grow soon, I think I will go with #2 sql.
Million thanks for your time and help, and sorry for the confusion I caused.
 
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
 

Attachments

You must log in or register to reply here.

Similar threads

K
Solved How to solve this Top 1 Query?
Replies
6
Views
1,102
GaP42
G
K
Ordered By Warning in Sql Server
Replies
8
Views
3,190
KitaYama
K
B
SQL Query Challenge: Finding the Latest Records
Replies
4
Views
1,621
ByteMyzer
B
AlexN
Solved Problem in designing the right query(ies)
2
Replies
30
Views
2,828
AlexN
AlexN
T
Solved Is it possible to find and display the max value between a series of fields?
Replies
10
Views
1,671
GPGeorge
GPGeorge

Users who are viewing this thread

  • ... and 1 more.
    • Total: 4 (members: 2, guests: 2)
    Back
    Top Bottom