September 7, 2010 at 10:18 pm
Comments posted to this topic are about the item HAVING EXISTS
September 7, 2010 at 10:46 pm
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. 😉
September 8, 2010 at 12:22 am
This was removed by the editor as SPAM
September 8, 2010 at 12:35 am
Great question. I got it wrong, obviously, but I learned something.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 8, 2010 at 12:41 am
Thanks For Question
I learned Having Exists ...!
September 8, 2010 at 1:45 am
Great question! Thank You Sir!
Realy flexible, handy and readable method.
Hrvoje Piasevoli
September 8, 2010 at 3:40 am
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://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
September 8, 2010 at 4:35 am
Great question... I was another victim, but I learned something new here today, which hopefully I'll remember 🙂
September 8, 2010 at 5:33 am
Definitely a tricky one. Hugo, do you have an application of this?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
September 8, 2010 at 5:46 am
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! :w00t: Nice question!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 8, 2010 at 5:59 am
Thanks, all, for the kind words!
jcrawf02 (9/8/2010)
Hugo, do you have an application of this?
Yes, I have. Although that particular situation called for a HAVING NOT EXISTS.
I can't share the actual details, so I'll have to pretend it was about an order processing system. The requirement was to find those orders from a particular customer where no other order on the same date (but by any customer) had the same number of order items. (I know this requirement makes no sense at all; just trust me that it does make sense in the actual context, which I can't disclose).
This is how I did it:
SELECT o.OrderNo, o.OrderDate,
SUM(oi.Amount) AS TotalOrderAmount
FROM Orders AS o
INNER JOIN OrderItems AS oi
ON oi.OrderNo = o.OrderNo
WHERE o.CustomerNo = @CustomerNo
GROUP BY o.OrderNo, o.OrderDate
HAVING NOT EXISTS
(SELECT *
FROM Orders AS o2
INNER JOIN OrderItems AS oi2
ON oi2.OrderNo = o2.OrderNo
WHERE o2.OrderNo <> o.OrderNo
AND o2.OrderDate = o.OrderDate
GROUP BY o2.OrderNo
HAVING COUNT(oi2.ItemCode) <> COUNT(oi.ItemCode));
I could also have solved this in a different way, by changing the HAVING clause of the outermost to
HAVING COUNT(oi.ItemCode) <> ALL
(SELECT COUNT(oi2.ItemCode)
FROM Orders AS o2
INNER JOIN OrderItems AS oi2
ON oi2.OrderNo = o2.OrderNo
WHERE o2.OrderNo <> o.OrderNo
AND o2.OrderDate = o.OrderDate
GROUP BY o2.OrderNo);
But frankly, I believe the ALL operator to be even more obscure than HAVING EXISTS. 😀
(EDIT: Proved my own point by using ANY instead of ALL when I posted this - now corrected)
September 8, 2010 at 6:58 am
QoTD has really been kicking my butt the last couple of weeks. I had to reread the explanation a couple of times to understand it. Good question, thanks.
September 8, 2010 at 7:32 am
Agreed excellent question. Took me a bit to understand the subtlety of it. Then just as I understood it, I discovered what was causing me to seriously scratch my head.
So the result of this query will be a list of all groups with a maximum for TheValue of less than 2.
The list of all groups with a maximum for the group with a max less than 3 not 2.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 8, 2010 at 7:40 am
All right, I confess. I don't get it. the subquery says where b GREATER THAN a not LESS THAN. As near as I can tell, there can never be anything GREATER THAN a? I could understad >= perhaps but not this.
I'm sure there's a moronically simple explanation but I didn't understand the explanation provided. No reflection on Hugo, I think my pea brain simply requires further clarity.
"Beliefs" get in the way of learning.
September 8, 2010 at 7:47 am
Robert Frasca (9/8/2010)
All right, I confess. I don't get it. the subquery says where b GREATER THAN a not LESS THAN. As near as I can tell, there can never be anything GREATER THAN a? I could understad >= perhaps but not this.I'm sure there's a moronically simple explanation but I didn't understand the explanation provided. No reflection on Hugo, I think my pea brain simply requires further clarity.
this is what i struggled with for a bit myself. The having will return the row when there is another group in the table with TheValue > the current group. Keep in mind that in the subquery, max(a.TheValue) is for the current group only. I had to create a table with the data in it before I totally got it myself. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply