What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 133 Stack Exchange communities.

I want to learn how to compose a query for Stack Exchange Data Explorer to sort comments in a single answer (for example, in this answer) based on the number of up votes.

My attempt is as follow, but I don't know how to enter the paramater.

select Score, Text, UserDisplayName
from Comments where PostId=19356
order by Score desc

Could you help me?

share|improve this question

migrated from stackoverflow.com Mar 21 '13 at 13:30

This question came from our site for professional and enthusiast programmers.

    
There already is a query that does something similar: data.stackexchange.com/stackoverflow/query/7991/… –  Martijn Pieters Mar 21 '13 at 13:32
    
@Dukeling What the?!... why does the dataexplorer tag exist on [Main] then? –  Danny Beckett Mar 21 '13 at 13:32
    
@DannyBeckett: because almost anyone can create a tag these days? There are 4 questions with that tag, including this migrated question... –  Martijn Pieters Mar 21 '13 at 13:33
    
@MartijnPieters And 2 of them are mine... hides –  Danny Beckett Mar 21 '13 at 13:33
    
I think you have the wrong post ID, this links to an answer with no comments: stackoverflow.com/questions/19347/… –  Richard J. Ross III Mar 21 '13 at 14:01

1 Answer 1

up vote 1 down vote accepted

Your provided example will work, assuming you have the correct PostId. For example, here's what your query looks like on Why is processing a sorted array faster than an unsorted array?'s most popular answer:

http://data.stackexchange.com/stackoverflow/query/104340?opt.textResults=true

select Score, Text, UserDisplayName
from Comments where PostId=11227902
order by Score desc
Score Text                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             UserDisplayName 
----- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- 
508   Given the amount of attention this is getting, I'm going to go ahead and expand my answer to include a layman explanation of branch predictors. This question has quite an amazing example of this situation.                                                                                                                                                                                                                                                                                                                    null            
67    I would like to point out that the reason the Intel compiler does the loop swap is actually far more impressive than just to help out branch prediction.  The loops written as is are almost 100% guaranteed to cause millions of cache misses on every iteration of the outer loop.  If you flip the two loops, you get at most 32768 cache misses (disregarding os preemption.)  A missed branch prediction here costs nanoseconds.  A cache miss can cause milliseconds if it has to fetch from memory.  That's a 10000000x improvement.null            
64    One way you can make the train analogy better is if you say that the only way the operator can know if the switch is correct is if the captain gives him a thumbs up or thumbs down, and the captain sits at the back of the train such that the operator can only see him when the captain passes him. This way if the switch is incorrect the train would have to stop, back up, and then take the correct route.                                                                                                              null            
57    Wow, that thing the ICC did is indeed miraculous...                                                                                                                                                                                                                                                                                                                                                                                                                                                                              null            
11    Note that with the "hack" (which is equivalent to the `cmovge` optimization gcc does with -O3, as noted in my comment to the question) it might be possible that the speed is a bit slower than in the case where branch prediction works "perfectly". So this is once more a case where you might want to optimize your code not only for the data structure, but also for its contents.                                                                                                                                        null            
10    I'm amazed at both the question and the answer - this has explained something I only barley knew about.  But it raises a question for me.  Should you optimize your code to take into account things like branch prediction?  Or would that be a case of pre-mature optimization?  Knowing the data you are processing would seem to drive the implementation.                                                                                                                                                                   null            
10    @J-16SDiZ Note that I did mention that it is not strictly equivalent to the original if-statement. `data[x]` is always between `0` and `255` so it doesn't get near that corner case.                                                                                                                                                                                                                                                                                                                                            null            
8     @PeterM I would say that branch prediction is not something that you should worry about when you design code. Stuff like memory and cache is much more important. However, bottlenecks from branch mispredictions can be very hard to detect unless you have access to hardware counters.                                                                                                                                                                                                                                        null            
8     I'm accepting this one because it provides a manual solution to get around the discrepancy.                                                                                                                                                                                                                                                                                                                                                                                                                                      null            
6     The pipelining/hardware optimization course I took last semester has really opened my eyes to things like this. It's a shame my university only requires it for CE degrees and not CS degrees as well...                                                                                                                                                                                                                                                                                                                         null            
6     I'd be interested to know what clang does here.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  null            
6     @Steve That's because processors can execute multiple instructions at the same time. They don't wait until the previous instructions finish before it starts the next one. Take a look at: http://en.wikipedia.org/wiki/Instruction_pipeline The diagram shows the overlap of instruction execution. When you have a branch misprediction, you lose that overlap because it gets interrupted.                                                                                                                                    null            
6     Sorry for asking the stupid question. In the code given by the person asking the question, the time to sort isn't calculated in his benchmark. What about in the benchmarks you gave in your answer? If the problem required a new set of data every time, would sorting it still come out ahead? I would imagine perhaps the amount of data would change that answer? And what would the break even point be (probably dependent on sorting algorithm used)?                                                                    null            
5     @Steve Yes, that's the downside of branch prediction and [speculative execution](http://en.wikipedia.org/wiki/Speculative_execution). You pay a heavier cost during a mispredict. But it pays off in the end because branches are (usually) correctly predicted many more times than not.                                                                                                                                                                                                                                        null            
5     @MichaelGraczyk I agree with most of what you say. But that `10000000x` is extremely exaggerated. My tests show that the loop-interchange is "only" 2-3x improvement over the sorted cases for GCC and VC++. Note that the data fits entirely into the L2 cache. So no memory access is required beyond the initial data generation. Even when the data doesn't fit in cache, I'd seriously doubt there would be a `10000000x` speedup. `100 - 1000x` would be more realistic.                                                   null            
3     Couldn't they have devised a system where the engineers flashed their lights or blew the whistle to tell the switchman which way to flip the switch?                                                                                                                                                                                                                                                                                                                                                                             null            
3     This is one of the best posts I have ever seen in SO! congratulations! I would love to give you 10 votes, but at least you have one more :)                                                                                                                                                                                                                                                                                                                                                                                      null            
2     @dhruvbird I never tested that myself, but I can almost guarantee that the results would be the same. A similar question came up [here](http://stackoverflow.com/a/12480386/922184) and [here](http://stackoverflow.com/a/12482649/922184) where somebody tested it for a range of thresholds. The worst performance was in always halfway between.                                                                                                                                                                              null            
2     @MarcBernier: In code terms, sure! http://stackoverflow.com/questions/1851299/is-it-possible-to-tell-the-branch-predictor-how-likely-it-is-to-follow-the-branc                                                                                                                                                                                                                                                                                                                                                                   null            
2     your hack fail when `data[x] = -2147483646`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     null            
2     @Mystical ... What a difference on i72600K@3.4 Using NB 7.1.2 java 1.7.0_04 x86_64 == Before optimization: 4.424609 After: 2.530692                                                                                                                                                                                                                                                                                                                                                                                              null            
1     @timmied You're not the first to ask that. :) [See this question.](http://stackoverflow.com/q/12026061/922184)                                                                                                                                                                                                                                                                                                                                                                                                                   null            
1     +1 @GManNickG: Although this topic is really awesome, I think the original example is somewhat invalid. That code is required if the data is unsorted, but if _we know_ that the data is sorted a better algorithm can be used: `for (c=arraySize-1; c>=0; --c) { sum+=data[c]; if (data[c]= 0? Does it make the running times of both versions (sorted & unsorted) the same?                                                                                                                                                                                                                                                                                                                                                                                                          null            
null  Yes, I agree with you on multiple side-effects path... Thanks for the answers.                                                                                                                                                                                                                                                                                                                                                                                                                                                   null            
null  Interesting, Im fairly new to this level. Can someone explain the bitwise operation to me? I don't know C#                                                                                                                                                                                                                                                                                                                                                                                                                       

...

(58 row(s) affected)

As far as going about getting that post ID, it's rather simple.

  1. Go to a post that you're interested in, such as How to write iOS app purely in C
  2. Underneath the post you are interested in, click the 'share' button, and copy out the text:

  3. That text should be in this format:

    http://stackoverflow.com/a/POST_ID/USER_ID

  4. From there, strip out the post ID, and you're good!

share|improve this answer
    
How to know the id of an answer? –  stalking is prohibited Mar 21 '13 at 14:07
    
@Karl'sstudents complete –  Richard J. Ross III Mar 21 '13 at 14:10
    
Why does UserDisplayName contain no data? –  stalking is prohibited Mar 21 '13 at 14:15
    
@Karl'sstudents I'm not entirely sure, but I think it only appears if the user is deleted. See this question: meta.stackexchange.com/questions/2677/… –  Richard J. Ross III Mar 21 '13 at 14:16
    
Maybe we need to use join from Users and Comments to get the user's DisplayName. –  stalking is prohibited Mar 21 '13 at 14:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .