Top (1) doesn't work in a complicated tsql program
In its simplest form I can get the first record of several by using the top (1). For example, this works fine: SELECT TOP (1) GiftFactID FROM FACT_SolicitorGift
WHERE (GiftFactID = 83532)
But I need to use this in a more complicated program. I am linking a gift with the name and address of the person who gave then gift and then going back and trying to grab the first solicitor in the fact_solicitorgift file so the gift isn't duplicated on a report. This is being done in a subquery because not every gift will have a solicitor record in the file. I'm including just that portion of code that addresses this issue. I end up getting no names when I use the Top (1) code. If I remove that portion of the code I end up getting 2 records - which I don't want... I only want the top record.
.... LEFT OUTER JOIN
(SELECT top (1) SG.GiftFactID, SG.ConstituentDimID, SC.FullName AS giftsolname
FROM FACT_SolicitorGift AS SG INNER JOIN
DIM_Constituent AS SC ON SG.SolicitorConstituentDim
WHERE (GiftFactID = 83532)
But I need to use this in a more complicated program. I am linking a gift with the name and address of the person who gave then gift and then going back and trying to grab the first solicitor in the fact_solicitorgift file so the gift isn't duplicated on a report. This is being done in a subquery because not every gift will have a solicitor record in the file. I'm including just that portion of code that addresses this issue. I end up getting no names when I use the Top (1) code. If I remove that portion of the code I end up getting 2 records - which I don't want... I only want the top record.
.... LEFT OUTER JOIN
(SELECT top (1) SG.GiftFactID, SG.ConstituentDimID, SC.FullName AS giftsolname
FROM FACT_SolicitorGift AS SG INNER JOIN
DIM_Constituent AS SC ON SG.SolicitorConstituentDim
BrandonGalderisi
Top 1 based on what criteria? I assume some chronological condition but you don't define one.
ASKER
Each gift has a distinct incremental number that is the key. I am using this key to join to the solicitor table. Because 2 or 3 solicitors could be assigned to one gift this distinct number in the gift table has 2 or 3 records in the solicitor table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I thought the top (1) would take the first record it finds with that key. I will definintely try your suggestion in the morning. I didn't realize I could do a group on a sub query so will definitely try this. Thanks
No. The derived table will be processed and then matched to the external results.
If GiftFactID is not unique in "FACT_SolicitorGift" then the "GROUP BY" method could return incorrect results. The returned GiftSolName would not match with the returned ConstituentDimID.
What you need is to use OUTER APPLY.
Note that you must also use ORDER BY along with TOP 1 to specify which of the records you want returned
What you need is to use OUTER APPLY.
Note that you must also use ORDER BY along with TOP 1 to specify which of the records you want returned
....
FACT_Gift AS F OUTER APPLY
(SELECT top (1) SG.GiftFactID, SG.ConstituentDimID, SC.FullName AS giftsolname
FROM FACT_SolicitorGift AS SG INNER JOIN
DIM_Constituent AS SC ON SG.SolicitorConstituentDimID = SC.ConstituentDimID
WHERE SG.GiftFactID = F.GiftFactID
ORDER BY SC.ConstituentDimID DESC) AS G
>>I thought the top (1) would take the first record it finds with that key. <<
As Brandon has already suggested, there is no concept of "first record" in SQL Server. TOP without an ORDER BY is meaningless. The only time you want to use that is when you don't care which one it returns.
As Brandon has already suggested, there is no concept of "first record" in SQL Server. TOP without an ORDER BY is meaningless. The only time you want to use that is when you don't care which one it returns.
ASKER
The outer apply didn't work - it says it isn't supported. I'm on sql 2005 right now - will be upgrading in the next month. I did try suggestion by Brandon and did get a record to print, which will work for now.
Ideally it would be great if I could list all of the gift solicitors in one field but I was even less sure how to accomplish that!
Ideally it would be great if I could list all of the gift solicitors in one field but I was even less sure how to accomplish that!
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>The outer apply didn't work - it says it isn't supported. I'm on sql 2005 right now<<
It is supported in SQL Server 2005, see here:
http://msdn.microsoft.com/en-us/library/ms175156(v=SQL.90).aspx
The problem has to be that you still have the Compatibility Level set to 80 (SQL Server 2000). So even if you upgrade to SQL Server 2012, it probbably will not help until you change the Compatibility Level.
It is supported in SQL Server 2005, see here:
http://msdn.microsoft.com/en-us/library/ms175156(v=SQL.90).aspx
The problem has to be that you still have the Compatibility Level set to 80 (SQL Server 2000). So even if you upgrade to SQL Server 2012, it probbably will not help until you change the Compatibility Level.
ASKER
That article on the queries was excellent! Thanks for pointing me in that direction. And I did check the compatibility level and it is set for 90. Not quite sure why it wasn't working for me.