HAVING

Question

Post reply

HAVING

Viewing 15 posts - 16 through 30 (of 55 total)

You must be logged in to reply to this topic. Login to reply

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 1

Yep - that's exactly what I did and that makes perfect sense now :hehe:

Thanks

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 2

I hate to admit, but I simply didn't know you can use HAVING without a Group By. 🙁

isddarms Old Hand Points: 352 More actions · Answer 3

isddarms

Old Hand

Points: 352

June 7, 2010 at 7:54 am

#1177081

W/D

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 4

UMG Developer (6/6/2010)
Great question, I had to think about it, thanks!
Though I would have to say I would have made one of the choices "One row displaying the value 1", as that would have made one of the incorrect choices more plausible.

If that was one of the choices, I would have got it wrong for sure, because I would have bitten on on it hard. Since it wasn't, I had to try if for my self.

Either way, I learned 🙂

Converting oxygen into carbon dioxide, since 1955.

webrunner SSC-Dedicated Points: 31848 More actions · Answer 5

Wow, that question was excellent. It really threw me for a loop and taught me something about the HAVING clause that I never knew before.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 6

This is a very cool question, thank you Hugo. In addition to requiring some brain racking to understand and answer it correctly, it is also the best example I have seen which so clearly demonstrates the order of execution in group by queries. Though the query in question does not spell out the group by explicitly, it is still a group by query:

scan the table to find all rows satisfying the predicate (where col2 <> 4)

group the results to calculate the requested aggeragate

check whether aggregated row to be shown later satisfies post-grouping predicate (having max(col2) > 5)

show the results (select)

Oleg

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Great question Hugo.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Rick Lang Mr or Mrs. 500 Points: 526 More actions · Answer 8

I'm missing something here. I get the same answer if I don't include the HAVING clause, so I'm not picking up on the value added of the clause.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 9

Rick Lang (6/7/2010)
I'm missing something here. I get the same answer if I don't include the HAVING clause, so I'm not picking up on the value added of the clause.

You might consider the HAVING clause as a simple way of expressing a WHERE EXISTS subquery.

An excellent question! Thanks Hugo.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 10

Rick Lang (6/7/2010)
I'm missing something here. I get the same answer if I don't include the HAVING clause, so I'm not picking up on the value added of the clause.

This is by design. Though there is no group by, this query returns aggregated results (one row), but only after it first checks whether having max(col2) > 5 predicate is satisfied. Because max(col2) = 6, this means that having is satisfied and therefore it does not change the result (one row still returns). This has the effect of enabling you to comment out the having line without changing the output. If you were to change

having max(col2) > 5

to

having max(col2) > 6

then no rows will be returned.

The point of the question was to check our understanding of the execution order and also to check whether it is legal to have a having clause without explicit group by.

Oleg

jlennartz SSCommitted Points: 1574 More actions · Answer 11

Okay if any one of the rows returned by the where clause, when checked by the having clause, evaluates to true then they all are considered true? And in this case will return a count of 5.

Thus the having clause doesn't narrow the result set it just determines if the count determined by the where clause will be returned or an empty set to be returned?

Yes?

sknox SSChampion Points: 12388 More actions · Answer 12

jlennartz (6/7/2010)
Okay if any one of the rows returned by the where clause, when checked by the having clause, evaluates to true then they all are considered true? And in this case will return a count of 5.
Thus the having clause doesn't narrow the result set it just determines if the count determined by the where clause will be returned or an empty set to be returned?
Yes?

No. (But almost.) If any row returned by the WHERE clause (i.e, the intermediate result set) satisfies the condition of the HAVING clause, that the set which that row belongs to will be included in the aggregated (i.e, final) result set. So if we added a GROUP BY col1 between the WHERE and HAVING clauses, we'd get a count of 2 because only [2,6] has col2 greater than 5 (so all rows [1,x] don't satisfy the HAVING clause) and [2,4] is eliminated by the WHERE clause, leaving [2,5] and [2,6] to count in the COUNT(*).

The HAVING clause DOES narrow the final result set. It just happens in this case that it narrows it from 1 result to 0 results.

We're working here with an aggregate query with no grouping. So there will always be either 0 or 1 result, because any results will be aggregagted into one total. (Before today I would have said there will always be 1 result, because I don't use HAVING very often, and wouldn't have thought to use it without a GROUP BY.)

The HAVING clause does NOT narrow the intermediate result set, as it is designed to act on the aggregated result set only. It inspects the intermediate result set, but acts on the aggregated result set. Clear as mud?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

I just checked back on the discussion to see if there are any questions that I need to answer, but I see you guys are doing a great job of working out the answers between yourselves. Kudos!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

KevinC. SSCommitted Points: 1570 More actions · Answer 14

Great question, Hugo. It made me do some research.

Thanks.

jlennartz SSCommitted Points: 1574 More actions · Answer 15

Thanks sknox, it is clearer than it was this morning.

Jerry