Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Top (1) doesn't work in a complicated tsql program

Posted on 2011-10-19
11
Medium Priority
?
833 Views
Last Modified: 2012-05-12
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
Comment
Question by:cindyfiller
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36996377
Top 1 based on what criteria?  I assume some chronological condition but you don't define one.
0
 

Author Comment

by:cindyfiller
ID: 36996405
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 36996435
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cindyfiller
ID: 36996451
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36996499
No.  The derived table will be processed and then matched to the external results.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36997199
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36997412
>>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
 

Author Comment

by:cindyfiller
ID: 36999552
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 36999987
please read this article, it should help to get the query solved:
http://www.experts-exchange.com/A_3203.html
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37002224
>>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
 

Author Comment

by:cindyfiller
ID: 37002501
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question