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

[cheekybuddha]

( What exactly are "inappropriate elements"? links to external websites? If so, what a shame.... there's some really good stuff out beyond these 4 walls! )

12 more posts and then you can post links (unless the threshold has increased from 100)

 

[KitaYama]

Some daze... I swear! This wasn't nearly as hard as I was making it out to be.

I wrote it another way... It returns what you asked for.

WITH ctePQ
AS (
SELECT
    PricesOrderedFromFK,
    PricesQuantity,
    Max(PricesRegisteredOn) As RegisteredOn,
    PricesRegisteredBy
FROM
    tblProductPrices
WHERE
    PricesProductKF = 34086
Group BY
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredBy
/* Order BY
    PricesQuantity,
    Max(PricesRegisteredOn)
*/
)
SELECT *
FROM ctePQ q
INNER JOIN tblProductPrices p
ON q.PricesOrderedFromFK = p.PricesOrderedFromFK
AND q.PricesQuantity = p.PricesQuantity
AND q.PricesRegisteredBy = p.PricesRegisteredBy
AND q.RegisteredOn = p.PricesRegisteredOn;

The CTE stuff is your original query without the Prices. In the final query at the bottom I just joined a copy of tblProductPrices to the common table expression (because it returns the small set you want) and then just added the extra columns. Super simple like it should have been to begin with. (Common table expressions are just sort of intermediate queries that you can reuse).

In your query, you have a WHERE clause that looks like it's a simplification so you can look at the rows and see if they make sense. You *may* need to add the ProductPriceFK to both queries and then add that to the join. (Sorry, it's supposed to say FK, not KF... I fat fingered it.)

Thanks for sticking with this.
At last, I'm free for a few hours to come back to this.
First of all, I'm confused. You suggested to use APPLY instead of ctePQ, but your last reply is not about APPLY. If we want to work with ctePQ, @DaveMD's suggestion in #22 works just fine.

And, Unfortunately, both your last two versions of the query returns errors.
Your last query gives me this error:
SQL Error [207] [S0001]: Invalid column name 'PricesProductKF'.
The error line is on this line:
PricesProductKF = 34086

If I run only the following select section, I don't receive any error.

SELECT
    PricesOrderedFromFK,
    PricesQuantity,
    Max(PricesRegisteredOn) As RegisteredOn,
    PricesRegisteredBy
FROM
    tblProductPrices
WHERE
    PricesProductFK = 34086
Group BY
    PricesOrderedFromFK,
    PricesQuantity,
    PricesRegisteredBy

Here's the script you asked for. It's really not anything special. As I said, the sample data in my first post shows it all.

USE [Orders]
GO

/****** Object:  Table [dbo].[tblProductPrices]    Script Date: 2026/04/02 06:22:21 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblProductPrices](
    [PricePK] [int] IDENTITY(1,1) NOT NULL,
    [PricesProductFK] [int] NOT NULL,
    [PricesOrderedFromFK] [int] NOT NULL,
    [PricesRegisteredOn] [datetime] NOT NULL,
    [PricesQuantity] [int] NOT NULL,
    [PricesUnitPrice] [smallmoney] NOT NULL,
    [PricesRegisteredBy] [int] NOT NULL,
    [PricesRemarks] [varchar](500) NULL,
 CONSTRAINT [PK_tblProductPrices] PRIMARY KEY CLUSTERED
(
    [PricePK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

 

[isladogs]

@KitaYama
I guess you missed the last line of @MadPiet's post:

(Sorry, it's supposed to say FK, not KF... I fat fingered it.)

 

[MadPiet]

Oh, change it back to ...FK (I messed it up when I created the table. That's why it doesn't work.)
In a nutshell, you can solve this without a common table expression too. You could also do something like this, which is even simpler:

SELECT *
FROM
(
    SELECT
        PricesOrderedFromFK,
        PricesQuantity,
        Max(PricesRegisteredOn) As RegisteredOn,
        PricesRegisteredBy
    FROM
        tblProductPrices
    WHERE
        PricesProductFK = 34086
    GROUP BY
        PricesOrderedFromFK,
        PricesQuantity,
        PricesRegisteredBy
) po
INNER JOIN
    tblProductPrices pp ON
    po.PricesOrderedFromFK = pp.PricesOrderedFromFK AND
    po.PricesQuantity = pp.PricesQuantity AND
    po.PricesRegisteredBy = pp.PricesRegisteredBy AND
    po.RegisteredOn = pp.PricesRegisteredOn

(You can specify which columns you want returned in the top SELECT statement... I was just being lazy. (Sorry Dave!))

last thing: when I ask a question on a board like this, I try to make it really clear what my situation looks like (so some CREATE TABLE and INSERT scripts to set up a similar environment so that I get some tested answers back. The way I see it is that the people who will be answering are, for the most part, a lot smarter than I am, so I want to make it as easy as I can for them to find my errors and/or come up with a tested solution. All that to say that learning to ask a concise question takes a little trial and error. (The same way that it takes some time to understand a question... like I had trouble with!) Really good concise writing is hard. Keep practicing! I find the best way to answer a question is like Jeff Moden posted on SQL Server Central... "

Forum Etiquette: How to post data/code on a forum to get the best help​

If you look it up on there, you'll find it. It's super advice. I've followed it many times and have even answered my own question by following his instructions.

Keep at it!

Pieter

 

[cheekybuddha]

I was just being lazy. (Sorry Dave!)

Yeah, you used code tags, but where are the emoticons?

 

[MadPiet]

If I can't even spell right, do you think I can do emoticons? =O

 

[KitaYama]

@KitaYama
I guess you missed the last line of @MadPiet's post:

Thanks. Yes, I had missed that. It seems when in hurry, one gets blind too.

@MadPiet If I change KF to FK, the error goes away, but the query returns 373 records.
I have only 11 records for PricesProductFK=34086.
To me, your query is like a Cartesian product query with so many results.

In a nutshell, you can solve this without a common table expression too. You could also do something like this, which is even simpler:

If we wanted to go through a simpler way, my own solution in #2 does the job.
Since the table is going to be a large one, we were trying to find a way to use a pass through query to ask sql server run the heavy job, not Access.
cte suggested by @DaveMD in #22 works fine and very fast. I was expecting to hear about APPLY you suggested, not going back to cte.
I'm working with Claud and chat to see if APPLY can be used in this case, but so far the suggested replies doesn't return the correct answer.
Thanks again for your time.

 

[KitaYama]

last thing: when I ask a question on a board like this, I try to make it really clear what my situation looks like (so some CREATE TABLE and INSERT scripts to set up a similar environment so that I get some tested answers back.

If this criticism is directed at me, I apologize for any issues I may have caused. During my two or three years here, I’ve seen many experts who can write complex queries just by looking at the initial data and the expected result. In my original post, I provided both, and I tried to be specific by outlining three rules I expected in the result.

I didn’t include a script because the table structure is very simple. The sample data I provided includes the actual field names, and I presented it in a table format for better clarity.

That said, you are correct. In future questions (if any), I will include the CREATE script as well. However, please keep in mind that mostly users in an organization do not have access to SQL Server directly. IT departments do not allow anyone to access the server due to security policies. As a result, those asking questions may not always be able to provide such scripts. It actually took me several days from my request until they mailed me what I needed (which I posted in #42)

Thanks again.

 

[MadPiet]

After thinking about it, APPLY isn't great for this use case. APPLY is for when you are joining a table to a table-valued function. (a good example of a table-valued function is TOP...

SELECT TOP (3) ProductID, UnitPrice
FROM Products
ORDER BY UnitPrice DESC

If this criticism is directed at me, I apologize for any issues I may have caused. During my two or three years here, I’ve seen many experts who can write complex queries just by looking at the initial data and the expected result. In my original post, I provided both, and I tried to be specific by outlining three rules I expected in the result.

I didn’t include a script because the table structure is very simple. The sample data I provided includes the actual field names, and I presented it in a table format for better clarity.

That said, you are correct. In future questions (if any), I will include the CREATE script as well. However, please keep in mind that we do not have access to SQL Server directly. Our IT department does not allow anyone to modify it due to security policies. As a result, those asking questions may not always be able to provide such scripts. It actually took me several days from my request until they mailed me what I needed (which I posted in #42)

Thanks again.

If this criticism is directed at me, I apologize for any issues I may have caused. During my two or three years here, I’ve seen many experts who can write complex queries just by looking at the initial data and the expected result. In my original post, I provided both, and I tried to be specific by outlining three rules I expected in the result.

I didn’t include a script because the table structure is very simple. The sample data I provided includes the actual field names, and I presented it in a table format for better clarity.

That said, you are correct. In future questions (if any), I will include the CREATE script as well. However, please keep in mind that we do not have access to SQL Server directly. Our IT department does not allow anyone to modify it due to security policies. As a result, those asking questions may not always be able to provide such scripts. It actually took me several days from my request until they mailed me what I needed (which I posted in #42)

Thanks again.

Sorry, didn't mean it as a criticism at all. The handy thing about create table and insert scripts is that they make reproducing the problem really easy. Apologies for the misunderstanding.