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 Wednesday, September 8, 2010 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 5,984, Visits: 8,242
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)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #982221
Posted Wednesday, September 8, 2010 6:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:18 PM
Points: 2,818, Visits: 2,561
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.
Post #982256
Posted Wednesday, September 8, 2010 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 13,315, Visits: 12,182
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 Moden's 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)
Post #982281
Posted Wednesday, September 8, 2010 7:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:33 AM
Points: 551, Visits: 652
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.
Post #982293
Posted Wednesday, September 8, 2010 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 13,315, Visits: 12,182
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.


_______________________________________________________________

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 Moden's 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)
Post #982302
Posted Wednesday, September 8, 2010 7:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 1,415, Visits: 1,832
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
Post #982305
Posted Wednesday, September 8, 2010 8:36 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:56 AM
Points: 632, Visits: 234
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
Post #982355
Posted Wednesday, September 8, 2010 8:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:33 AM
Points: 551, Visits: 652
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.
Post #982356
Posted Wednesday, September 8, 2010 8:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #982361
Posted Wednesday, September 8, 2010 8:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:33 AM
Points: 554, Visits: 1,196
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

Post #982362
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse