• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

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.SolicitorConstituentDimID = SC.ConstituentDimID)   AS G ON G.GiftFactID = FACT_Gift.GiftFactID
0
cindyfiller
Asked:
cindyfiller
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
BrandonGalderisiCommented:
Top 1 based on what criteria?  I assume some chronological condition but you don't define one.
0
 
cindyfillerAuthor Commented:
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.
0
 
BrandonGalderisiCommented:
But how will this return a single record relevant to your other tables in your 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.SolicitorConstituentDimID = SC.ConstituentDimID


I'm thinking you maybe want something like:

 .... LEFT OUTER JOIN
 (SELECT   SG.GiftFactID, min(SG.ConstituentDimID) as ConstituentDimID, min(SC.FullName) AS giftsolname
  FROM   FACT_SolicitorGift AS SG INNER JOIN
  DIM_Constituent AS SC ON SG.SolicitorConstituentDimID = SC.ConstituentDimID
group by sf.GiftFactID
)   AS G ON G.GiftFactID = FACT_Gift.GiftFactID
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
cindyfillerAuthor Commented:
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
0
 
BrandonGalderisiCommented:
No.  The derived table will be processed and then matched to the external results.
0
 
ThomasianCommented:
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
....
 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

Open in new window

0
 
Anthony PerkinsCommented:
>>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.
0
 
cindyfillerAuthor Commented:
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!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please read this article, it should help to get the query solved:
http://www.experts-exchange.com/A_3203.html
0
 
Anthony PerkinsCommented:
>>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.
0
 
cindyfillerAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now