Find the Most Recent Post

Question

Find the Most Recent Post

brian.thiessen

SSC Veteran

Points: 237
More actions
November 23, 2014 at 7:43 am

#295255

Comments posted to this topic are about the item Find the Most Recent Post
November 24, 2014 at 1:42 am

This was removed by the editor as SPAM

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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: 259085 More actions · Answer 2

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: 13921 More actions · Answer 3

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.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

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

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

Umer Akram

SSC-Insane

Points: 22209

November 24, 2014 at 1:13 am

#1760410

Very nice question, Thanks for sharing

domenico.delbrocco SSChasing Mays Points: 617 More actions · Answer 6

+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

hjp Default port Points: 1437 More actions · Answer 7

+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 SSCertifiable Points: 5791 More actions · Answer 8

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: 104773 More actions · Answer 9

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: 104773 More actions · Answer 10

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: 259085 More actions · Answer 11

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 SSCertifiable Points: 5791 More actions · Answer 12

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: 2153 More actions · Answer 13

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