SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HAVING EXISTS


HAVING EXISTS

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19089 Visits: 12426
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. :-D

(EDIT: Proved my own point by using ANY instead of ALL when I posted this - now corrected)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Daniel Bowlin
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8184 Visits: 2629
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64450 Visits: 17976
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Robert Frasca
Robert Frasca
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1589 Visits: 798
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64450 Visits: 17976
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3341 Visits: 2149
Robert Frasca (9/8/2010)
..., there can never be anything GREATER THAN a?...


Robert, I believe the key is to treat the groups individually. Therefore, when Group 1 from "a" is being evaluated (because of the group by), it is being evaluated against the whole QotD table "b".

Here, we are looking for the GroupNames from the grouped set "a" whose MAX(a.TheValue) values are less than the "b.TheValue"

Since the MAX(a.TheValue) for Group 1 is 3, which is same as the max value for "b" it does not appear in the result set.
The MAX(a.TheValue) for Group 2 is 2, which is less than "b", and therefore it appears in the result set.

Hope that clears things out a bit. You can break the query into two, with the first part being:


SELECT a.GroupName, MAX(a.TheValue)
FROM QotD AS a
GROUP BY a.GroupName, a.TheValue



Now compare this with "b" - you should start getting a feel of the internal workings.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Tony Bater
Tony Bater
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 261
In a recent blog, Kalen Delaney said she is frequently asked why is there a need to understand the internals of SQL Server. I think this question illustrates why. The key to getting the right answer is an understanding of the sequence of events during the query processing - the HAVING clause is processed after the GROUP BY and acts on the result set from the earlier phases.

I learned this from Itzik Ben-Gan's book 'Inside Microsoft SQL Server 2008: T-SQL Querying'. Chapter 1, Logical Query Processing, has a great section on the sequence of events that occurs when the query processor gets to work. I really recommend this book to anyone who is struggling to understand the explanation for this QotD.


Tony
Robert Frasca
Robert Frasca
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1589 Visits: 798
Group 1 - max(a) is 3 ---- nothing in b is greater than a
Group 2 - max(a) is 2 ---- 3 (in b which is the whole table) is greater than 2 so this is true
Group 3 - max(a) is 3 ---- nothing in b is greater than a
Group 4 - max(a) is 3 ---- nothing in b is greater than a
Group 5 - max(a) is 1 ---- 2 & 3 (in b which is the whole table) are greater than 1 so this is true
Group 6 - max(a) is 2 ---- 3 (in b which is the whole table) is greater than 2 so this is true
Group 7 - max(a) is 3 ---- nothing in b is greater than a

Okay, I've got it now. I think I was getting my a and b contexts confused. Thanks for the help folks. It probably helped that I increased my blood caffeine level.

GREAT question. The amount of smoke coming out of my ears has doubled.

"Beliefs" get in the way of learning.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19089 Visits: 12426
A while back, I promised to post the QotD code in a copy/paste ready format in the discussion when I have the code as a graphic in the question. Yet, I always forget to do as I promised. My apologies to all those who already spent time typing it in for yourself.

And to those who want to try it out, and experiment to see how small changes affect the result, here is some SQL you can copy and paste:
CREATE TABLE QotD 
(GroupName varchar(20) NOT NULL,
TheValue int NOT NULL,
PRIMARY KEY (GroupName, TheValue)
);
go
INSERT INTO QotD (GroupName, TheValue)
SELECT 'Group 1', 1 UNION ALL
SELECT 'Group 1', 2 UNION ALL
SELECT 'Group 1', 3 UNION ALL
SELECT 'Group 2', 1 UNION ALL
SELECT 'Group 2', 2 UNION ALL
SELECT 'Group 3', 1 UNION ALL
SELECT 'Group 3', 3 UNION ALL
SELECT 'Group 4', 2 UNION ALL
SELECT 'Group 4', 3 UNION ALL
SELECT 'Group 5', 1 UNION ALL
SELECT 'Group 6', 2 UNION ALL
SELECT 'Group 7', 3;
go
SELECT a.GroupName --, MAX(a.TheValue)
FROM QotD AS a
GROUP BY a.GroupName
HAVING EXISTS (SELECT *
FROM QotD AS b
WHERE b.TheValue > MAX(a.TheValue));
go
DROP TABLE QotD;
go



EDIT: I added an extra column, commented out, to the SELECT. This might help you see what happens.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Bradley Deem
Bradley Deem
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 Visits: 1248
Thanks for the question Hugo and the practical application example. Your explanation of the QOTD from two days ago provided a great refresher and helped me get it right.

In regards to your practice application example, assuming I understand the requirement, I believe there is a mistake in the having clause. See the comment in the first select statement.

Here is a full working example with set up and tear down.


CREATE TABLE Orders(OrderNo INT,OrderDate DATETIME,CustomerNo INT)
CREATE TABLE OrderItems(OrderNo INT,Amount INT, ItemCode VARCHAR(10))

INSERT Orders
VALUES(100,'9-8-10',1),(101,'9-8-10',2),(102,'9-8-10',3)

INSERT OrderItems
VALUES(100,1,'Bike'),(100,2,'Tire'),(101,2,'Spokes'),(102,3,'Pedal')

DECLARE @CustomerNo AS INT = 1

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)); -- Changed the <> to =

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 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);

DROP TABLE Orders
DROP TABLE OrderItems


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search