Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-14 : 13:08:29
|
Ok I am out of my league here and I need some guidance. I have done pretty basic queries and stored procedures but I am stuck on this query which is hopefully simple for you guys.Here is the query as I have tried it.SELECT items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONumFROM items INNER JOIN porders ON items.PONum = porders.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorIDWHERE (items.PartNum = '2122K256') AND (porders.Date = (SELECT MAX(Date) AS Expr1 FROM porders AS lookup WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster - Carr Supply') I have three tables. porders, items, and vendors. Porders and Items have a one to many relationship. Vendors is just to query by the VendorName instead of VendorID.I am trying to do a select query but I want to pull the records that match my inputs (PartNum & VendorName) but I only want to pull up the record that in the using the highest Date in the porders.Date field. So basically it should only be returning one record. Mine is returning 10 records. Because there will be several records that match my query I just want to get the record with the highest date. Anyone know how to do that?Thanks,Stacy |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-14 : 14:11:02
|
Try adding another condition to the where clause of the inner query as shown below. However, the logic is not entirely clear to me. Can one Partnum+VendorName combination have more than one PONum on the same date?WHERE (items.PartNum = '2122K256') AND ( porders.Date =( SELECT MAX(Date) AS Expr1 FROM porders AS lookup WHERE (Date = porders.Date) AND lookup.PONum = porders.PONum ) ) AND (vendors.VendorName LIKE 'McMaster - Carr Supply') |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-14 : 14:29:42
|
quote: Originally posted by sunitabeck Try adding another condition to the where clause of the inner query as shown below. However, the logic is not entirely clear to me. Can one Partnum+VendorName combination have more than one PONum on the same date?WHERE (items.PartNum = '2122K256') AND ( porders.Date =( SELECT MAX(Date) AS Expr1 FROM porders AS lookup WHERE (Date = porders.Date) AND lookup.PONum = porders.PONum ) ) AND (vendors.VendorName LIKE 'McMaster - Carr Supply')
It would not be likely that there would be duplicate PO's on the same date for the same part from the same vendor. I actually don't even need to return the porders.Date field I just want to use it to return the fields in the other tables but for the PO with the highest date. The way my query is now it is returning all the PO's that have that part# for specified Vendor. I only want it to return basically the last time it was ordered (highest Date).Stacy |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-25 : 15:16:44
|
I get an error adding in the red code from above. Error - The multi-part identifier "lookup-PONum" could not be bound. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-25 : 15:24:51
|
quote: Originally posted by StacyOW I get an error adding in the red code from above. Error - The multi-part identifier "lookup-PONum" could not be bound.
Did you use "lookup.PONum" or "lookup-PONum"? It should be the former. |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-26 : 10:55:26
|
I entered it as lookup.PONum and I still get the error. This is what I have right now.SELECT items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONumFROM items INNER JOIN porders ON items.PONum = porders.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorIDWHERE (items.PartNum = '2122K256') AND (porders.Date = (SELECT MAX(Date) AS Expr1 FROM porders AS lookup WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-26 : 11:28:03
|
Does the table porders have a column named PONum? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-26 : 11:55:18
|
quote: Originally posted by StacyOW I entered it as lookup.PONum and I still get the error. This is what I have right now.SELECT items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONumFROM items INNER JOIN porders ON items.PONum = porders.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorIDWHERE (items.PartNum = '2122K256') AND (porders.Date = (SELECT MAX(Date) AS Expr1 FROM porders AS lookup WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum))
is this the full query?if yes, you're missing some braces------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-26 : 15:42:38
|
quote: Originally posted by sunitabeck Does the table porders have a column named PONum?
Yes the PONum is my Key field and it is auto-increment. |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-26 : 15:44:44
|
Yes this is the query as I have it right now. Where am I missing braces?Thank you for your help.Stacyquote: Originally posted by visakh16
quote: Originally posted by StacyOW I entered it as lookup.PONum and I still get the error. This is what I have right now.SELECT items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONumFROM items INNER JOIN porders ON items.PONum = porders.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorIDWHERE (items.PartNum = '2122K256') AND (porders.Date = (SELECT MAX(Date) AS Expr1 FROM porders AS lookup WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum))
is this the full query?if yes, you're missing some braces------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-26 : 15:50:47
|
If I do the following query it works but I don't get just the Max Date I get every record that the part was ordered from McMaster. I only want to get the Highest Date or the last time it was ordered.SELECT items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONumFROM items INNER JOIN porders ON items.PONum = porders.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorIDWHERE (items.PartNum = '2122K256') AND (porders.Date = (SELECT MAX(Date) AS Expr1 FROM porders AS lookup WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-26 : 16:21:32
|
I'm not sure the query makes sense. Specifically, the predicate is correlated on the DATE. So, you are going to get a MAX date where the date = date..?Here is a guess at what you want. But, if that doesn’t work, it’ll be easier if you can post the DDL, DML and Expected output so we can help you better. Here are some links that might help with that:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxSELECT items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.[Date], items.ItemID, items.PONumFROM items INNER JOIN porders ON items.PONum = porders.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorIDINNER JOIN ( SELECT PONum, MAX([Date]) AS MaxOrderDate FROM porders GROUP BY PONum ) AS MaxOrder ON porders.PONum = MaxOrder.PONum AND porders.[Date] = MaxOrder.MaxOrderDateWHERE items.PartNum = '2122K256' AND vendors.VendorName LIKE 'McMaster - Carr Supply' EDIT: Forgot to move the vedor reference out of the derived-table. |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2012-09-26 : 16:38:18
|
My suggestion is close to Lamprey's, except I the porders table is only joined through subselect.select items.EquipmentID ,items.DepartmentID ,items.Type ,items.ComponentNum ,items.PartNum ,items.ItemDescription ,items.QtyOrd ,items.QtyRcvd ,items.UnitPrice ,items.ItemUpdate ,items.Price ,items.Copy ,items.WorkNum ,items.UpdateOrdered ,items.Modified ,porders.Date ,items.ItemID ,items.PONum from items inner join (select PONum ,max(Date) as Date from porders group by PONum ) as porders on porders.PONum=items.PONum inner join vendors on vendors.VendorID=porders.VendorID where items.PartNum='2122K256' and vendors.VendorName like 'McMaster' |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-26 : 17:06:19
|
BitsMead, you lost the ability to join to vendors on the VendorID. But, I like the though process. Perhaps change from MAX(Date) and GROUP BY to use a ROW_NUMBER funciton so you can include the VendorID..? |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2012-09-26 : 17:17:07
|
@Lamprey: You are right - I totally missed that.@StacyOW: Forget my suggestion and go with Lamprey's suggestionEdit: I really really need to learn the over() function. I think this might work, but my experience with over(), is very very limitedselect items.EquipmentID ,items.DepartmentID ,items.Type ,items.ComponentNum ,items.PartNum ,items.ItemDescription ,items.QtyOrd ,items.QtyRcvd ,items.UnitPrice ,items.ItemUpdate ,items.Price ,items.Copy ,items.WorkNum ,items.UpdateOrdered ,items.Modified ,porders.Date ,items.ItemID ,items.PONum from items inner join (select row_number() over(partition PONum order by Date desc) as rownum ,* from porders ) as porders on porders.PONum=items.PONum and porders.rownum=1 inner join vendors on vendors.VendorID=porders.VendorID where items.PartNum='2122K256' and vendors.VendorName like 'McMaster' |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-26 : 19:20:43
|
Tried that query but I get lots of things underlined, specifically Incorrect syntax near 'PONum'.I'm not sure how else to explain what I am trying to do - than in my first post. quote: I have three tables. porders, items, and vendors joined together. Porders and Items have a one to many relationship. Vendors is linked to porders by VendorID just so I can query by the VendorName instead of VendorID.I am trying to do a select query but I want to pull the records (from the porders table) that match my variables for PartNum & VendorName but I only want to pull up the record that has the highest Date. By the porders.Date field. So basically it should only be returning one record. Mine is returning 10 records. The query I was showing in my original post was returning all of the records for McMaster with my variable Part#. I only want to get the last time it was ordered or the highest date!
quote: Originally posted by bitsmed @Lamprey: You are right - I totally missed that.@StacyOW: Forget my suggestion and go with Lamprey's suggestionEdit: I really really need to learn the over() function. I think this might work, but my experience with over(), is very very limitedselect items.EquipmentID ,items.DepartmentID ,items.Type ,items.ComponentNum ,items.PartNum ,items.ItemDescription ,items.QtyOrd ,items.QtyRcvd ,items.UnitPrice ,items.ItemUpdate ,items.Price ,items.Copy ,items.WorkNum ,items.UpdateOrdered ,items.Modified ,porders.Date ,items.ItemID ,items.PONum from items inner join (select row_number() over(partition PONum order by Date desc) as rownum ,* from porders ) as porders on porders.PONum=items.PONum and porders.rownum=1 inner join vendors on vendors.VendorID=porders.VendorID where items.PartNum='2122K256' and vendors.VendorName like 'McMaster'
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-26 : 19:31:01
|
I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword: over(partition BY PONum order by Date desc) http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-27 : 20:30:21
|
quote: Originally posted by Lamprey I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword: over(partition BY PONum order by Date desc) http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
I'm not sure what a DDL or DML is but I have provide the query as I was trying to do it, which didn't work. I can give you the table fields if that will help, but I can't see how. I already had the table linked in my tried query in my example.The porders table looks like this.PONum int Unchecked - Key - auto-incrementVendorID int CheckedOriginatorID int CheckedStatusID int CheckedShippedID int CheckedDate date UncheckedMPONum varchar(10) CheckedDateRequired date CheckedRefPONum real CheckedShipTo varchar(30) CheckedShipAddress varchar(25) CheckedShipCity varchar(15) CheckedShipPhone varchar(12) CheckedShipState varchar(2) CheckedShipZip varchar(10) CheckedTax decimal(18, 2) CheckedFreight decimal(18, 2) CheckedPrintStatus varchar(3) CheckedPOTotal money CheckedComments varchar(250) CheckedDescription varchar(250) CheckedModified timestamp Unchecked The Items Table looks like this.ItemID int Unchecked - key - auto-incrementPONum int UncheckedEquipmentID int UncheckedDepartmentID int UncheckedType varchar(1) CheckedComponentNum int CheckedPartNum varchar(50) CheckedItemDescription varchar(150) CheckedQtyOrd real CheckedQtyRcvd real CheckedUnitPrice real CheckedItemUpdate varchar(3) CheckedPrice real CheckedCopy varchar(3) CheckedWorkNum varchar(12) CheckedUpdateOrdered varchar(3) CheckedModified timestamp Unchecked The Vendor table looks like this...VendorID int Unchecked - Key - Auto-incrementVendorName varchar(50) UncheckedAddress varchar(50) CheckedAddress2 varchar(50) CheckedCity varchar(50) CheckedState varchar(50) CheckedZip varchar(10) CheckedPhone varchar(50) CheckedFax varchar(50) CheckedVendorType varchar(50) CheckedProductsServices varchar(250) CheckedComments varchar(250) CheckedMinOrdAmnt varchar(25) CheckedWebAddress varchar(50) CheckedModified timestamp Unchecked The query I had originally tried was in my first post. It would give me what I want but not the Highest/Max Date - it would return All the records from that Vendor for that Part#, not the last time it was ordered.Sorry I am new to this and not sure exactly what you need. I thought by giving you the query sample and the explanation of what I was trying to do would be enough. I can't believe it is this hard to do a max date query. Just when I was really starting to like sql.Stacy |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-09-30 : 11:56:28
|
quote: Originally posted by Lamprey I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword: over(partition BY PONum order by Date desc) http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
tried the partition By but I still get 15 records it is not just returning the highest date.Thanks for all you help with this.Stacy |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-01 : 10:24:16
|
can you try providing some sample data from your tables and try explaining the output you want from them?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-10-24 : 14:00:57
|
quote: Originally posted by visakh16 can you try providing some sample data from your tables and try explaining the output you want from them?I'm not exactly sure how to provide you with sample data. I have a form on which the user enters the date, a vendorID, etc. which is in the porders table. I have an items table linked one to many to the porders table and the items table has all of the items that are ordered for each porder. From the items table when the user enters a part# and then leaves that field I wanted to run a query to bring up the last time (Max Date) that part# was ordered from that Vendor. I can get the query to run but I brings up every time that part# was ordered from the vendor not just one record/the LAST time it was ordered. I have attached an image of the table data my query is returning at this point, so you can see that it is returning 11 records. I just can't seem to get it to do the max date part of the query. Perhaps there is some other way to do a query like this but in my old database program I used MaxDate so I just thought that I would use that in SQL server also.Sorry I can't seem to find a way to add an image from my computer to this post.Thanks for all your help.Stacy------------------------------------------------------------------------------------------------------
It would look this this though...EquipmentID DepartmentID Type ComponentNum PartNum ItemDescription QtyOrd QtyRcvd UnitPrice Price Date ItemID PoNum VendorNameI don't know if that helps. I could email you the image if that would help. Let me know.Stacy |
|
|
Next Page
|
|
|
|
|