Post reply

Odd Man Out

  • November 20, 2014 at 7:01 am

    #1759822

    It is better to be lucky then good...

    I knew they were all functions, but this morning I guessed correctly.

    Interesting questions.

  • November 20, 2014 at 7:26 am

    #1759834

    ksatpute123 (11/20/2014)

    I sure did a smart thing by adding the SQL SERVER 2008 R2 reference.

    🙂

    Being precise really cann't be bad thing.

  • November 20, 2014 at 7:27 am

    #1759836

    Koen Verbeeck (11/20/2014)

    Toreador (11/20/2014)

    GROUPING_ID was introduced in SQL2008. The others were all available in SQL2005.

    And VARP is the only function with a name less than 5 characters! :w00t: 😎 :hehe:

    But CHECKSUM_AGG is the only one whose first character has an ASCII value less than 100. 😎

    I too struggled to figure out what was used to determine the "odd man out" so I went with the only windowing function...it seemed to be the most likely of any of the choices.

    _______________________________________________________________

    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/

  • November 20, 2014 at 7:30 am

    #1759837

    Sean Lange (11/20/2014)

    Koen Verbeeck (11/20/2014)

    Toreador (11/20/2014)

    GROUPING_ID was introduced in SQL2008. The others were all available in SQL2005.

    And VARP is the only function with a name less than 5 characters! :w00t: 😎 :hehe:

    But CHECKSUM_AGG is the only one whose first character has an ASCII value less than 100. 😎

    I too struggled to figure out what was used to determine the "odd man out" so I went with the only windowing function...it seemed to be the most likely of any of the choices.

    That's exactly how I approached it.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 20, 2014 at 7:30 am

    #1759838

    Even though BOL lists it as an aggregation function, I'd argue that GROUPING_ID doesn't aggregate anything. Rather, it gives an indication if a row contains aggregated values or not.

    Gerald Britton, Pluralsight courses

  • November 20, 2014 at 7:35 am

    #1759840

    Sean Lange (11/20/2014)

    I too struggled to figure out what was used to determine the "odd man out" so I went with the only windowing function...it seemed to be the most likely of any of the choices.

    I get what you mean with "only windowing function", but ROW_NUMBER() is a ranking windowing function. The other functions can use an OVER clause and are by definition "aggregate windowing functions". Not sure you would ever use those functions like that though...

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

  • November 20, 2014 at 7:37 am

    #1759842

    W.A.G. and a hit.

    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

  • November 20, 2014 at 7:38 am

    #1759843

    Koen Verbeeck (11/20/2014)

    Sean Lange (11/20/2014)

    I too struggled to figure out what was used to determine the "odd man out" so I went with the only windowing function...it seemed to be the most likely of any of the choices.

    I get what you mean with "only windowing function", but ROW_NUMBER() is a ranking windowing function. The other functions can use an OVER clause and are by definition "aggregate windowing functions". Not sure you would ever use those functions like that though...

    Oh I know...I was making a guess and an assumption. There could be any number of of them that are the one that isn't like the other.

    Suddenly I am singing Sesame Street "One of these things is not like the other, one of these things is kind of the same". 😛

    _______________________________________________________________

    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/

  • November 20, 2014 at 8:18 am

    #1759859

    Sean Lange (11/20/2014)

    Koen Verbeeck (11/20/2014)

    Sean Lange (11/20/2014)

    I too struggled to figure out what was used to determine the "odd man out" so I went with the only windowing function...it seemed to be the most likely of any of the choices.

    I get what you mean with "only windowing function", but ROW_NUMBER() is a ranking windowing function. The other functions can use an OVER clause and are by definition "aggregate windowing functions". Not sure you would ever use those functions like that though...

    Oh I know...I was making a guess and an assumption. There could be any number of of them that are the one that isn't like the other.

    Suddenly I am singing Sesame Street "One of these things is not like the other, one of these things is kind of the same". 😛

    Odd man out!!

    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

  • November 20, 2014 at 9:10 am

    #1759882

    Koen Verbeeck (11/20/2014)

    I'd argue ROWCOUNT_BIG is an odd one as well, as it is not calculated over the current result set, but over the results of a previous query.

    +1

    Also, ROWCOUNT_BIG requires a second query which makes it different from the others. Maybe the author meant "COUNT_BIG()"

    Don Simpson


    I'm not sure about Heisenberg.

  • November 20, 2014 at 12:20 pm

    #1759943

    DonlSimpson (11/20/2014)

    Koen Verbeeck (11/20/2014)

    I'd argue ROWCOUNT_BIG is an odd one as well, as it is not calculated over the current result set, but over the results of a previous query.

    +1

    Also, ROWCOUNT_BIG requires a second query which makes it different from the others. Maybe the author meant "COUNT_BIG()"

    That was my guess too until I saw that he was relying on the error in 2008R2 documentation, because I just couldn't imagine anyone thinking of ROWCOUNT_BIG as an aggregate function. Apparently MS suggested it was and corrected the documentation for the next release but didn't bother to fix the original error.

    Tom

  • November 20, 2014 at 6:06 pm

    #1759988

    Koen Verbeeck (11/20/2014)

    I'd argue ROWCOUNT_BIG is an odd one as well, as it is not calculated over the current result set, but over the results of a previous query.

    That's exactly why I picked it.

  • November 20, 2014 at 6:18 pm

    #1759991

    Sean Lange (11/20/2014)

    ...

    Suddenly I am singing Sesame Street "One of these things is not like the other, one of these things is kind of the same". 😛

    Oh, great. Now you've got the song going through my head. 🙂

  • November 21, 2014 at 8:30 am

    #1760181

    marcia.j.wilson (11/20/2014)

    Sean Lange (11/20/2014)

    ...

    Suddenly I am singing Sesame Street "One of these things is not like the other, one of these things is kind of the same". 😛

    Oh, great. Now you've got the song going through my head. 🙂

    hehe. You're welcome. 😀

    _______________________________________________________________

    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/

  • November 24, 2014 at 9:18 am

    #1760564

    Thanks for the question.

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

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