October 10, 2011 at 8:06 am
I have severasl itema in a table, including multiple POs for the same thing. I only want to return the maximum PO for each part number.
I know this is not the most efficient way, but I think I'm close.
Thanks in advance for your help.
SELECT tblPODetail.PONum, tblPODetail.PartNum, tblPODetail.UnitCost
FROM [SharePointProgress].[dbo].tblPODetail S1
WHERE tblPODetail.PONum = (SELECT MAX(tblPODetail.PONum)
FROM [SharePointProgress].[dbo].tblPODetail S2
WHERE S1.PartNum = S2.PartNum)
Produces
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "tblPODetail.PONum" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "tblPODetail.PONum" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblPODetail.PONum" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblPODetail.PartNum" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblPODetail.UnitCost" could not be bound.
October 10, 2011 at 8:10 am
Definitely not the most straight forward approach to this. does something like this work for you?
SELECT max(tblPODetail.PONum), tblPODetail.PartNum, tblPODetail.UnitCost
FROM [SharePointProgress].[dbo].tblPODetail
group by tblPODetail.PartNum, tblPODetail.UnitCost
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 8:11 am
Oh yeah, the reason you are getting those error is because you aliased your tables and still trying to refer to them by their original name.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 8:27 am
Thank you very much!!
October 10, 2011 at 8:35 am
Happy to help. Hope that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply