HAVING

  • Comments posted to this topic are about the item HAVING


    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/

  • 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.

  • Thanks, UMG!

    And you are right, that would have been a better distractor. I'm not sure why I included the "one row, value 2" option. It's a while back I made this question; if I recall correctly, the HAVING clause first tested for >= 5, and I changed this later but apparently forgot to change the distractor.


    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/

  • thanks Hugo, i really didnt know this. I was assuming that HAVING must have a corresponding GROUP by.

  • Bit Tricky & thanks for the Good question 🙂

  • High quality, thats what I like in the mornings - Thanks Hugo!

    Best Regards,

    Chris Büttner

  • Good Question, we will be always thinking that having can be used only with the Group By clause.

  • Nice question. Learned something about HAVING.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Nice question! I got it wrong, because I misinterpreted the HAVING clause; I read it as WHERE Col >= 5, and that leads to a value of 2. I knew the behaviour of HAVING though, but I wanted to have it play a role in this query. But alas, it's only to confuse the reader...

    So there can always be logic in an incorrect answer, as long as you read the question wrong 🙂

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • I thought it would be an empty result but wasn't positive decided to "test" and created the table. I got this message on executing the query:

    Msg 8121, Level 16, State 1, Line 4

    Column 'QotD.col2' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

    This is from BOL:

    "The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. However, HAVING can be specified without GROUP BY. The HAVING clause specifies additional filters that are applied after the WHERE clause filters. These filters can be applied to an aggregate function used in the select list."

    I don't know if the version of SQL makes a difference, but I am using SQL Server 2008 R2

    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.

  • Hugo Kornelis (6/6/2010)


    Thanks, UMG!

    And you are right, that would have been a better distractor. I'm not sure why I included the "one row, value 2" option. It's a while back I made this question; if I recall correctly, the HAVING clause first tested for >= 5, and I changed this later but apparently forgot to change the distractor.

    Hugo, I think the "one row, value 2" answer was for people so used to group by that they instinctively grouped by col1. Then the HAVING would limit it to col1=2, and the WHERE would have eliminated #4, so you'd get a count of 2. I know I ALMOST made that mistake. :w00t:

  • I am not sure I understand the logic behind this one.

    This query will result in a single unnamed column with a value of 5

    SELECT COUNT(*)

    FROM QotD

    WHERE col2 <> 4

    How does adding the

    HAVING MAX(col2) > 5

    end up with the same result?

    Thanks,

  • Putting MAX(col2) in the result set helped me see what was going on. WHERE clause evaluated first giving one row result set. This one row satisfies HAVING clause.

    select COUNT(*), MAX(col2)

    from @QotD

    where col2 <> 4

    having MAX(col2) > 5

  • MG-148046 (6/7/2010)


    I thought it would be an empty result but wasn't positive decided to "test" and created the table. I got this message on executing the query:

    Msg 8121, Level 16, State 1, Line 4

    Column 'QotD.col2' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

    This is from BOL:

    "The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. However, HAVING can be specified without GROUP BY. The HAVING clause specifies additional filters that are applied after the WHERE clause filters. These filters can be applied to an aggregate function used in the select list."

    I don't know if the version of SQL makes a difference, but I am using SQL Server 2008 R2

    Hi MG,

    You didn't post the code you used, but I think that you used

    HAVING Col2 > 5

    whereas the QotD question uses

    HAVING MAX(Col2) > 5


    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/

  • dbowlin (6/7/2010)


    I am not sure I understand the logic behind this one.

    This query will result in a single unnamed column with a value of 5

    SELECT COUNT(*)

    FROM QotD

    WHERE col2 <> 4

    How does adding the

    HAVING MAX(col2) > 5

    end up with the same result?

    Thanks,

    Hi dbowlin,

    The reply posted by wware might give you a clue.

    The first form is quite well known. We all use SELECT COUNT(*) FROM SomeTable, or SELECT MAX(SomeColumn) FROM SomeTable occasionaly, and expect SQL Server to calculate the aggregate over the entire table, returning a single row.

    The HAVING builds on that. There still is a single group containing all rows (except those filtered by the WHERE). If that group satisfies the HAVING, you get a result. If it doesn't the group is discarded and since there are no other groups, the result is empty.


    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/

Viewing 15 posts - 1 through 15 (of 55 total)

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