last day (599 days later) » 

Mark Hurd
Mark Hurd
15:03
data.stackexchange.com/stackoverflow/query/65774/…
The above query attempts to list the top 10 Gold, Silver and Bronze Tag Badge holders.
It works if you comment out the Unions and only keep one Select statement.
Whatever is wrong is not affected by the With clause; not using it produces the same "wrong" result.
Does anyone know if the fault is with my SQL or with SQL Server?
Mark Hurd
Mark Hurd
15:20
room topic changed to Data Explorer Discussions: Discussing Data Explorer Issues and Queries [data-explorer]
Tim Stone
Tim Stone
So what's wrong with it? (sorry, my brain is having trouble waking up this morning)
Popular Demand
Popular Demand
I don't have a problem with your question or this room, but if you have an SQL question I suspect you'll get slightly better results asking on chat.SO than chat. MSO.
Mark Hurd
Mark Hurd
Firstly you can see the full query is wrong because Jon Skeet is not mentioned :-)
If you comment out all from before the first Union All to before the Order By you get correct results for Gold, and similarly for the others.
But when you combine them in a Union, some how the results change.
Tim Stone
Tim Stone
Oh, yes.
It has to do with the evaluation of the ORDER BY in relation to the SELECT TOP
Or wait.
Mark Hurd
Mark Hurd
15:55
This query attempts to test the Top in Select Union with Order By, but it works, as is.
BTW the above query only works as text for me on IE8.
Tim Stone
Tim Stone
Really? I'll take a look later, need to find my netbook that has IE8 on it.
Mark Hurd
Mark Hurd
The failure to display the result in IE8 is not due to the single letter column names I was originally using.
Tim Stone
Tim Stone
16:15
@MarkHurd Well, I'm pretty sure the issue in your original query was related to the SELECT TOP not being impacted by your intended sort order, but I can't say for certain.
That aside, this query seems to do what you want.
I avoided having to do UNIONs by partitioning the TOP n groups with RECORD_NUMBER() over the desired sort order.
Mark Hurd
Mark Hurd
Well done. I'll report your query at the answer where it is needed.
Tim Stone
Tim Stone
Feel free to dress it up with a title and description, looks like I forgot to do that, heh. :P
Mark Hurd
Mark Hurd
Tomorrow I'll push my select tops in to sub queries with their own order bys to confirm that was the problem, but it is almost 2am here so I should go to bed.
Tim Stone
Tim Stone
Sounds like a good idea, good night!
Ah, think there was a slight bug if the site being queried didn't have enough people who had earned tag badges at the respective level, fixed now.
Mark Hurd
Mark Hurd
16:53
2
A: Badge Statistics and maximum number of badges on Stack Overflow

Mark HurdTop Tag Badge Counts Select Top 10 UserId As [User Link], Count(*) As NumTagBadges From Badges Where Name In (Select TagName from Tags Where IsNull(TagName,'')<>'') Group By UserId Order By NumTagBadges Desc Top 4: Jon Skeet - 387 Marc Gravell - 157 Darin Dimitrov - 132 BalusC - 11...

Updated answer above.

  last day (599 days later) »