JKeith
August 20, 2025, 2:20pm
1
So I have a subquery that’s sorted by last po header date (trying to find the last purchased price)
Pretty Straightforward top
Just joining by the part number
Now, for this part number, I know there’s only 1 po out there for it, and if I convert it to a standard innerSubquery the value returns fine, but that won’t always be the case.
I’ve seen some comments on having to use RANK but that just doesn’t seem to be necessary for something this simple (and if we hit a common part I don’t want to put that additional load on the DB server)
Thoughts?
Search for @Banderson ’s posts on SQL Window Functions. I think that’s what you want.
2 Likes
Randy
August 20, 2025, 2:23pm
3
+1 to what Mark says above. Plus, it’s really hard to try and help when you don’t post your query.
1 Like
Banderson
August 20, 2025, 2:24pm
4
If you just need one field, (the last purchased price) I would do an inner sub select. But that only works for one field.
This post explains how to do that.
yes you can with a few limitations. Being that there may not be a previous op, you may return a null value. You have to use a inner sub query and that will limit you to returning only one column. If you need multiple columns with this method you may just have to make more than one query. This is the only method i know of at this time, I’m sure there may be other methods.
create a inner sub query
[image]
bring in the joboper table (you’ll need to assign an alias), create links under subqu…
2 Likes
JKeith
August 20, 2025, 2:42pm
5
OK, I’ve tried using row_number, and it returns in SQL, but not baq, I’m missing something silly here
Banderson
August 20, 2025, 2:47pm
6
Mostly leaving out the screen shots that show us the rest of what you are doing.
Did you add a display field to your top level query from the subquery?
1 Like
JKeith
August 20, 2025, 2:51pm
7
I think I may have figured it out …
JKeith
August 20, 2025, 2:53pm
8
Helps if you partition over the part number
2 Likes
