February 11, 2015 at 12:53 pm
My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.
Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,
dr.strSn, Convert(varchar(10),
dr.dtWarrantyStart, 111) WS,
Convert(varchar(10), dr.dtWarrantyEnd , 111) WE,
strPoNum,
strTransaction,
dr.intDurableId,
mn.strRank + ' ' + mn.strFullName NAME,
dr.intModelId,
m.intMakeId,
dr.bitAssetScheduler,
r.strComputerName
FromDrat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN (Select Top 1 strSn, strComputerName from Drat_Registration group by strSN, strComputerName, dttRegistration) r on r.strSN = dr.strSN
February 11, 2015 at 1:11 pm
Try running this and see what it returns:
Select strSn, strComputerName from Drat_Registration group by strSN, strComputerName, dttRegistration
Also, note the without an ORDER BY, you don't really know which pair of strSn, strCompterName pairs will be returned with the TOP 1.
February 11, 2015 at 1:16 pm
Hi,
it return multiple records on that side of the Join, but I only want the top record of that group, heres example
strSN Computername
10Z2VK1 MNLPS-X43C0-A3
10Z2VK1 NGMNB-7XTAA-D6
10Z2VK1 NGMNNB-7XTAA-1E
10Z2VK1 NGMNNB-7XTAA-1E
10Z2VK1 NGMNNB-NOREX-A4
So you can see over time the same strSn can have multiple computer names, but I only want the last one registered which is the dttRegistration field.
Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,
dr.strSn, Convert(varchar(10),
dr.dtWarrantyStart, 111) WS,
Convert(varchar(10), dr.dtWarrantyEnd , 111) WE,
strPoNum,
strTransaction,
dr.intDurableId,
mn.strRank + ' ' + mn.strFullName NAME,
dr.intModelId,
m.intMakeId,
dr.bitAssetScheduler,
r.strComputerName
FromDrat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN (Select Top 1 strSn, strComputerName from Drat_Registration group by strSN, strComputerName, dttRegistration) r on r.strSN = dr.strSN
February 11, 2015 at 1:34 pm
Stubby Bunny (2/11/2015)
Hi,it return multiple records on that side of the Join, but I only want the top record of that group, heres example
strSN Computername
10Z2VK1 MNLPS-X43C0-A3
10Z2VK1 NGMNB-7XTAA-D6
10Z2VK1 NGMNNB-7XTAA-1E
10Z2VK1 NGMNNB-7XTAA-1E
10Z2VK1 NGMNNB-NOREX-A4
So you can see over time the same strSn can have multiple computer names, but I only want the last one registered which is the dttRegistration field.
Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,
dr.strSn, Convert(varchar(10),
dr.dtWarrantyStart, 111) WS,
Convert(varchar(10), dr.dtWarrantyEnd , 111) WE,
strPoNum,
strTransaction,
dr.intDurableId,
mn.strRank + ' ' + mn.strFullName NAME,
dr.intModelId,
m.intMakeId,
dr.bitAssetScheduler,
r.strComputerName
FromDrat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN (Select Top 1 strSn, strComputerName from Drat_Registration group by strSN, strComputerName, dttRegistration) r on r.strSN = dr.strSN
Problem, only you know what is in the dttRegistration column.
February 11, 2015 at 1:44 pm
HI,
Sry its a datefield.
February 11, 2015 at 2:27 pm
Also, looking back at what you posted, you only showed the data for one strSN. What you needed to look for was to see if any of them are blank.
If you want the newest (latest) you also need to include an ORDER BY dttRegistration DESC in the sub query.
February 11, 2015 at 2:49 pm
Hi,
I have tried all of that, but nothing returns with the Top 1 clause, if I take that out I get records but it multiplies them cause of the many registration records that come over. Only 1 record exists in Drat_Received but in drat registration many records with the same strSN can exist but I only need the newest which is datetime stamped with the dttRegistration field. If I do this only the very top record comes across, no other records.
February 11, 2015 at 2:56 pm
Well, I just had a Duh! moment.
Change the LEFT JOIN to this:
outer apply (Select Top 1 strSn, strComputerName from Drat_Registration dr1 where dr1.strSN = dr.strSN group by strSN, strComputerName order by dr1.dttRegistration desc)r(strSn, strComputerName )
February 11, 2015 at 2:58 pm
If you are wondering what the Duh! moment is, look at your sub query again. It will only return ONE row of data and that ONE row of data will only match ONE row of data in the main table, for all the others you will get a null.
February 11, 2015 at 3:01 pm
That fixed it thank you
February 11, 2015 at 3:01 pm
Stubby Bunny (2/11/2015)
Hi,I have tried all of that, but nothing returns with the Top 1 clause, if I take that out I get records but it multiplies them cause of the many registration records that come over. Only 1 record exists in Drat_Received but in drat registration many records with the same strSN can exist but I only need the newest which is datetime stamped with the dttRegistration field. If I do this only the very top record comes across, no other records.
Think about your subquery. It does not fire once per row, it fires once. Which means you are going to get 1 and only 1 value there because of the top 1. Since you have no order by it isn't always going to return the same row either.
There are no tables here for me to work so I am shooting in the dark but I think you need something more like this.
with SortedValues as
(
Select strSn
, strComputerName
, ROW_NUMBER() over (Partition by strSN, strComputerName order by dttRegistration desc) as RowNum
from Drat_Registration
group by strSN, strComputerName
)
Select dr.intRecId
, c.strCategory
, mm.strManufacturer
, dm.strMake
, m.strModel
, dr.strSn
, Convert(varchar(10), dr.dtWarrantyStart, 111) as WS
, Convert(varchar(10), dr.dtWarrantyEnd , 111) as WE
, strPoNum
, strTransaction
, dr.intDurableId
, mn.strRank + ' ' + mn.strFullName as NAME
, dr.intModelId
, m.intMakeId
, dr.bitAssetScheduler
, r.strComputerName
From Drat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN SortedValues sv on sv.strSN = dr.strSN and sv.RowNum = 1
_______________________________________________________________
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/
February 11, 2015 at 3:04 pm
Sean Lange (2/11/2015)
Stubby Bunny (2/11/2015)
Hi,I have tried all of that, but nothing returns with the Top 1 clause, if I take that out I get records but it multiplies them cause of the many registration records that come over. Only 1 record exists in Drat_Received but in drat registration many records with the same strSN can exist but I only need the newest which is datetime stamped with the dttRegistration field. If I do this only the very top record comes across, no other records.
Think about your subquery. It does not fire once per row, it fires once. Which means you are going to get 1 and only 1 value there because of the top 1. Since you have no order by it isn't always going to return the same row either.
There are no tables here for me to work so I am shooting in the dark but I think you need something more like this.
with SortedValues as
(
Select strSn
, strComputerName
, ROW_NUMBER() over (Partition by strSN, strComputerName order by dttRegistration desc) as RowNum
from Drat_Registration
group by strSN, strComputerName
)
Select dr.intRecId
, c.strCategory
, mm.strManufacturer
, dm.strMake
, m.strModel
, dr.strSn
, Convert(varchar(10), dr.dtWarrantyStart, 111) as WS
, Convert(varchar(10), dr.dtWarrantyEnd , 111) as WE
, strPoNum
, strTransaction
, dr.intDurableId
, mn.strRank + ' ' + mn.strFullName as NAME
, dr.intModelId
, m.intMakeId
, dr.bitAssetScheduler
, r.strComputerName
From Drat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN SortedValues sv on sv.strSN = dr.strSN and sv.RowNum = 1
Yes, that is another solution to the problem.
February 11, 2015 at 3:08 pm
Lynn Pettis (2/11/2015)
Sean Lange (2/11/2015)
Stubby Bunny (2/11/2015)
Hi,I have tried all of that, but nothing returns with the Top 1 clause, if I take that out I get records but it multiplies them cause of the many registration records that come over. Only 1 record exists in Drat_Received but in drat registration many records with the same strSN can exist but I only need the newest which is datetime stamped with the dttRegistration field. If I do this only the very top record comes across, no other records.
Think about your subquery. It does not fire once per row, it fires once. Which means you are going to get 1 and only 1 value there because of the top 1. Since you have no order by it isn't always going to return the same row either.
There are no tables here for me to work so I am shooting in the dark but I think you need something more like this.
with SortedValues as
(
Select strSn
, strComputerName
, ROW_NUMBER() over (Partition by strSN, strComputerName order by dttRegistration desc) as RowNum
from Drat_Registration
group by strSN, strComputerName
)
Select dr.intRecId
, c.strCategory
, mm.strManufacturer
, dm.strMake
, m.strModel
, dr.strSn
, Convert(varchar(10), dr.dtWarrantyStart, 111) as WS
, Convert(varchar(10), dr.dtWarrantyEnd , 111) as WE
, strPoNum
, strTransaction
, dr.intDurableId
, mn.strRank + ' ' + mn.strFullName as NAME
, dr.intModelId
, m.intMakeId
, dr.bitAssetScheduler
, r.strComputerName
From Drat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN SortedValues sv on sv.strSN = dr.strSN and sv.RowNum = 1
Yes, that is another solution to the problem.
Seems that your excellent solution was posted while I was in the middle of writing mine. I hadn't seen yours or your aha! moment when I posted. 😀
_______________________________________________________________
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/
February 11, 2015 at 3:10 pm
Sean Lange (2/11/2015)
Lynn Pettis (2/11/2015)
Sean Lange (2/11/2015)
Stubby Bunny (2/11/2015)
Hi,I have tried all of that, but nothing returns with the Top 1 clause, if I take that out I get records but it multiplies them cause of the many registration records that come over. Only 1 record exists in Drat_Received but in drat registration many records with the same strSN can exist but I only need the newest which is datetime stamped with the dttRegistration field. If I do this only the very top record comes across, no other records.
Think about your subquery. It does not fire once per row, it fires once. Which means you are going to get 1 and only 1 value there because of the top 1. Since you have no order by it isn't always going to return the same row either.
There are no tables here for me to work so I am shooting in the dark but I think you need something more like this.
with SortedValues as
(
Select strSn
, strComputerName
, ROW_NUMBER() over (Partition by strSN, strComputerName order by dttRegistration desc) as RowNum
from Drat_Registration
group by strSN, strComputerName
)
Select dr.intRecId
, c.strCategory
, mm.strManufacturer
, dm.strMake
, m.strModel
, dr.strSn
, Convert(varchar(10), dr.dtWarrantyStart, 111) as WS
, Convert(varchar(10), dr.dtWarrantyEnd , 111) as WE
, strPoNum
, strTransaction
, dr.intDurableId
, mn.strRank + ' ' + mn.strFullName as NAME
, dr.intModelId
, m.intMakeId
, dr.bitAssetScheduler
, r.strComputerName
From Drat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN SortedValues sv on sv.strSN = dr.strSN and sv.RowNum = 1
Yes, that is another solution to the problem.
Seems that your excellent solution was posted while I was in the middle of writing mine. I hadn't seen yours or your aha! moment when I posted. 😀
Well, I was only rewriting a small part of the code, not the entire query. 😉
February 11, 2015 at 3:12 pm
Lynn Pettis (2/11/2015)
Sean Lange (2/11/2015)
Lynn Pettis (2/11/2015)
Sean Lange (2/11/2015)
Stubby Bunny (2/11/2015)
Hi,I have tried all of that, but nothing returns with the Top 1 clause, if I take that out I get records but it multiplies them cause of the many registration records that come over. Only 1 record exists in Drat_Received but in drat registration many records with the same strSN can exist but I only need the newest which is datetime stamped with the dttRegistration field. If I do this only the very top record comes across, no other records.
Think about your subquery. It does not fire once per row, it fires once. Which means you are going to get 1 and only 1 value there because of the top 1. Since you have no order by it isn't always going to return the same row either.
There are no tables here for me to work so I am shooting in the dark but I think you need something more like this.
with SortedValues as
(
Select strSn
, strComputerName
, ROW_NUMBER() over (Partition by strSN, strComputerName order by dttRegistration desc) as RowNum
from Drat_Registration
group by strSN, strComputerName
)
Select dr.intRecId
, c.strCategory
, mm.strManufacturer
, dm.strMake
, m.strModel
, dr.strSn
, Convert(varchar(10), dr.dtWarrantyStart, 111) as WS
, Convert(varchar(10), dr.dtWarrantyEnd , 111) as WE
, strPoNum
, strTransaction
, dr.intDurableId
, mn.strRank + ' ' + mn.strFullName as NAME
, dr.intModelId
, m.intMakeId
, dr.bitAssetScheduler
, r.strComputerName
From Drat_Received dr
LEFT JOIN Drat_Model m on m.intModelId = dr.intModelId
LEFT JOIN Drat_Make dm on dm.intMakeId = m.intMakeId
LEFT JOIN Drat_Manufacturer mm on mm.intManufacturerId = dm.intManufacturerID
LEFT JOIN Drat_Category c on c.intCategoryID = mm.intCategoryID
LEFT JOIN Drat_PoNum po on po.intPoId = dr.intPOId
LEFT JOIN Drat_TransactionType tt on tt.intTransTypeId = dr.intTransTypeId
LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId
LEFT JOIN Personnel.dbo.tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo
LEFT JOIN SortedValues sv on sv.strSN = dr.strSN and sv.RowNum = 1
Yes, that is another solution to the problem.
Seems that your excellent solution was posted while I was in the middle of writing mine. I hadn't seen yours or your aha! moment when I posted. 😀
Well, I was only rewriting a small part of the code, not the entire query. 😉
LOL. I just reformatted it a little bit. I don't know why but having trailing commas just drives me nutty. There goes my OCD again...err I mean CDO (now they are in the proper order).
_______________________________________________________________
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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply