Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

HAVING EXISTS Expand / Collapse
Author
Message
Posted Tuesday, September 7, 2010 10:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
Comments posted to this topic are about the item HAVING EXISTS


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #982025
Posted Tuesday, September 7, 2010 10:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Great question Hugo, thanks!

I wasn't sure if that was allowed or not, so I had to do some research and learned something new. Note that I can imagine where I need to apply it, but you never know. ;)
Post #982036
Posted Wednesday, September 8, 2010 12:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 3,859, Visits: 5,001
Thanks for the question.
Learned something


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #982065
Posted Wednesday, September 8, 2010 12:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,283, Visits: 10,156
Great question. I got it wrong, obviously, but I learned something.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #982067
Posted Wednesday, September 8, 2010 12:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:46 PM
Points: 2,296, Visits: 1,425
Thanks For Question

I learned Having Exists ...!
Post #982069
Posted Wednesday, September 8, 2010 1:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, October 19, 2013 12:40 PM
Points: 361, Visits: 508
Great question! Thank You Sir!
Realy flexible, handy and readable method.


Hrvoje Piasevoli
Post #982090
Posted Wednesday, September 8, 2010 3:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 1,403, Visits: 1,814
Hello!
I believe the most important keys to solving this one (as you rightly mentioned) are to understand that:
1. Both the WHERE & HAVING are "binary" conditions, i.e. they would either evaluate to TRUE (meaning the row/group remains) or FALSE (meaning the row/group is filtered out)
2. The logical processing order evaluates this particular condition by group (i.e. uses the MAX(a.TheValue) for that group)
a. This is because HAVING is evaluated later on (after the GROUP BY)

I knew for sure about# 1, but #2 was a little too much to resist an experiment due to the use of the aggregation. I thus ended up running the query and confirmed my belief.


Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #982149
Posted Wednesday, September 8, 2010 4:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 987, Visits: 867
Great question... I was another victim, but I learned something new here today, which hopefully I'll remember


Post #982174
Posted Wednesday, September 8, 2010 5:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 2,658, Visits: 19,190
Definitely a tricky one. Hugo, do you have an application of this?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #982201
Posted Wednesday, September 8, 2010 5:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:26 AM
Points: 846, Visits: 850
I had to stare at it a long while, but I finally came up with the right answer without running any queries or researching... woo-hoo! Nice question!

ron


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #982212
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse