Find the Most Recent Post

  • brian.thiessen

    SSC Veteran

    Points: 237

    Comments posted to this topic are about the item Find the Most Recent Post

  • SQLRNNR

    SSC Guru

    Points: 281210

    While a decent question about an analytic function in SQL Server, I am not enthused by the terminology used in reference to the table variable.

    Table variables are not "memory tables." Table variables just like temp tables can be memory resident or can be disk resident. It is best to not call table variables a memory table to avoid perpetuating the myth that they are wholly memory resident.

    Here is a good proof on that for reference.

    http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

    I am also not terribly enthused by the framing of the question. The question as currently posed, requests to return only the id. No answer has just the id. Instead all return author and id.

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    Nice question. I have been bitten by the windowing functions defaults before, so this question was easy 😀

    (I even wrote a blog post about it: Beware the defaults! (in windowing functions)[/url]. Shameless plug :-). Somehow it didn't make it to the explanation :hehe:)

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

  • Hany Helmy

    SSChampion

    Points: 13321

    SQLRNNR (11/23/2014)


    While a decent question about an analytic function in SQL Server, I am not enthused by the terminology used in reference to the table variable.

    Table variables are not "memory tables." Table variables just like temp tables can be memory resident or can be disk resident. It is best to not call table variables a memory table to avoid perpetuating the myth that they are wholly memory resident.

    Here is a good proof on that for reference.

    http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

    I am also not terribly enthused by the framing of the question. The question as currently posed, requests to return only the id. No answer has just the id. Instead all return author and id.

    I couldn`t agree more, table variables are more temp tables over memory tables, but thanx anyway.

  • Hany Helmy

    SSChampion

    Points: 13321

    This question reminds me of the 70-461 exam questions 🙂

  • twin.devil

    SSC-Insane

    Points: 22208

    Very nice question, Thanks for sharing

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 586

    +1

    Nice question

    I haven't used First_Value() and Last_Value() before, but remembering the OVER syntax has helped me to guess the right answer

  • This was removed by the editor as SPAM

  • hjp

    Default port

    Points: 1434

    +1

    Nice question. And thanks for the example of "beware of the defaults", because to the naked eye #4 and #5 doesn't look to yield different results. But they do..

  • edwardwill

    SSCarpal Tunnel

    Points: 4907

    The question asked:

    Which of the following will return only the Id of the most recent post for each Author?

    The correct answer should have been "None of the above", since all of the options included columns that were not Id. The "correct" answer included the column Author.

    Or do we just ignore the specification? Or have I misunderstood the specification?

  • TomThomson

    SSC Guru

    Points: 104772

    edwardwill (11/24/2014)


    The question asked:

    Which of the following will return only the Id of the most recent post for each Author?

    The correct answer should have been "None of the above", since all of the options included columns that were not Id. The "correct" answer included the column Author.

    Or do we just ignore the specification? Or have I misunderstood the specification?

    The word "only" can cause horribly ambiguity in English. Does "only the X for each Y" mean "the X for each Y and no other Xs" or does it mean "the X for each Y and nothing else at all"? The answer clearly indicates the first meaning, but you are saying you think it has to be the second meaning; my view is that it means whichever the person who wrote it intended it to mean, and in this case which meaning that was could be deduced from the set of answers provided so there are no grounds for complaint.

    It's one of those problems that we inevitablu have when writing specifications or requirements in natural languages, instead of using formal systems like Z or VDM; of course formal systems can have ther problems too - hardly anyone can understand them, so they often are not the appropriate means of communication, and that's not their only problem.

    Tom

  • TomThomson

    SSC Guru

    Points: 104772

    Nice question, but I have two queries about it:

    (i) why 2 points? it's just extremely simple logic.

    (ii) why use first_value and DESC when last_value would do, so that the code could be 6 characters (including the space before DESC) shorter?

    Tom

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    TomThomson (11/24/2014)


    It's one of those problems that we inevitablu have when writing specifications or requirements in natural languages, instead of using formal systems like Z or VDM; of course formal systems can have ther problems too - hardly anyone can understand them, so they often are not the appropriate means of communication, and that's not their only problem.

    I still have nightmares about that university class in formal languages...

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

  • edwardwill

    SSCarpal Tunnel

    Points: 4907

    TomThomson (11/24/2014)


    It's one of those problems that we inevitablu have when writing specifications or requirements in natural languages, instead of using formal systems like Z or VDM; of course formal systems can have ther problems too - hardly anyone can understand them, so they often are not the appropriate means of communication, and that's not their only problem.

    I studied Z at University in the 1990s and absolutely loved it (I got 98% in my final exam module) but unfortunately, as you say, hardly anyone uses it. However, this could have been expressed unambiguously in Z, though I'm far too long out of the game to have a go!:-D

  • RLilj33

    SSCrazy

    Points: 2146

    Thanks for the question. I was looking for the earliest occurrence for each author so selected the wrong answer. Must be Monday - need more coffee. 🙂

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

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