August 1, 2008 at 11:51 am
I have had a similar issue recently but cant figure this one out. i have the below data in a table
RenewalIDRenewalReferenceContractIDRenewalStatusCodeRenewByDateRenewalPeriodNumber
3485116 2781741 2114717 ACT 01/10/2007 00:00 1
2793270 2781741 2114717 INA 01/10/2007 00:00 1
2793271 2781742 2114717 INA 01/10/2008 00:00 2
i need to return just the renewalID and perhaps the renewalReference for any renewalReference that has no ACT lines. so i would only get 2793271 2781742 returned here.
there will be many more cases like this in the table.
i can get the renewal reference this way but not the renewalID as it returns all rows, or all inactive rows if i put in a clause to check for ACT rows
select re.renewalreference as'count periodNumber'
from renewals as re
group by re.renewalPeriodNumber,re.renewalreference
having count(renewalreference) <2
thanks all
August 1, 2008 at 12:05 pm
Try this:
Select Distinct RenewalID, RenewalReference
From Renewals
Where RenewalReference NOT IN(
Select r2.RenewalReference
From Renewals r2
Where RenewalStatusCode = 'ACT'
)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 1, 2008 at 12:09 pm
cool, thanks, the query works,however i should have been clearer. i cant do this via joins. a subquery will not be very efficient when there are many thousands of rows in the table. is there a better way to do this with joins?
August 1, 2008 at 12:26 pm
winston Smith (8/1/2008)
cool, thanks, the query works,however i should have been clearer. i cant do this via joins. a subquery will not be very efficient when there are many thousands of rows in the table. is there a better way to do this with joins?
Subqueries are not generally slower or faster than equivilant Joins. If you have a specific performance issue with this query, then please post the table def, with keys and indexes, and I will be happy to tune it for you. Until then, I am just shooting in the dark.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 1, 2008 at 12:28 pm
EXISTS can be faster than IN (but only sometimes), so here is an example:
Select Distinct RenewalID, RenewalReference
From Renewals r1
Where NOT EXISTS( Select *
From Renewals r2
Where r2.RenewalStatusCode = 'ACT'
And r2.RenewalReference = r1.RenewalReference)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 1, 2008 at 12:33 pm
And, if you are really hung up on using JOin, here is another example, but really, there is no reason to beleive that this query will generally perform significantly different from the other two:
Select Distinct r1.RenewalID, r1.RenewalReference
From Renewals r1
Join Renewals r2 ON r2.RenewalStatusCode = 'ACT'
And r2.RenewalReference = r1.RenewalReference
Now specific circumstances can vary, so I would really need to know more about your tables, etc., to go any further.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply