Select .. Top 10 by Month?

Jeff Moden · March 25th, 2008, 08:23 PM

Quote:

quote:Originally posted by dparsons
Marvelous example Jeff. Nicely done. =]

Thanks, Doug...

--Jeff Moden

Jeff Moden · March 25th, 2008, 08:25 PM

Quote:

quote:Originally posted by rstelma
Hey Jeff,

Excellent example and solution. I was wondering about the WITH TIES clause. Without it the query still pulls Jay at 60 and Joe at 60 but not Jan at 60. Why is that?

Thanks,
Richard

Because it's no longer considering ties in the top 3 scores... it's just considering the top 3 "places" in descending order without any consideration for ties.

--Jeff Moden

crabjoe · March 26th, 2008, 08:39 AM

Thanks for all the help folks.. Now I have to look up "WITH TIES" to get a better understanding of how it works.

Jeff Moden · March 26th, 2008, 01:45 PM

Perfect... thanks for the feedback Joe... If you can't find anything to your satisfaction, lemme know and I'll try to explain better. One way of explaining it is to lookup RANKING in WikiPedia... then look for "DENSE RANKING (1223)". THAT's what "WITH TIES" sort of does...

Here's what it says...

Quote:

quote:Dense ranking ("1223" ranking)In dense ranking, items that compare equal receive the same ranking number, and the next item(s) receive the immediately following ranking number. Equivalently, each item's ranking number is 1 plus the number of items ranked above it that are distinct with respect to the ranking order.

Quote:

Thus if A ranks ahead of B and C (which compare equal) which are both ranked ahead of D, then A gets ranking number 1 ("first"), B gets ranking number 2 ("joint second"), C also gets ranking number 2 ("joint second") and D gets ranking number 3 ("third").

...and here's the link... http://en.wikipedia.org/wiki/Ranking

SO... let's say you have 1,2,2,2,2,2,3,4,4 and you ask for the TOP 3 WITH TIES, you'll get back everyone with the Top 3 scores...

1,2,2,2,2,2,3

If you ask for the TOP 3 but without ties, you get back the top 3 rows regardless...

1,2,2

--Jeff Moden

rstelma · March 26th, 2008, 05:11 PM

Jeff,

Thanks for the explanation of WITH TIES. Very much appreciated. Thank you.

Richard

Jeff Moden · March 27th, 2008, 08:27 AM

Quote:

quote:Originally posted by rstelma
Jeff,
Thanks for the explanation of WITH TIES. Very much appreciated. Thank you.

Richard

Thanks for the feedback, Richard... it's the best "payment" anyone could hope for on forums like this.

--Jeff Moden

crabjoe · March 27th, 2008, 11:29 AM

Yes Jeff, THANK YOU for the explanation on WITH TIES. I'd never seen it before, but now I know I'll be using it!!

rstelma · March 27th, 2008, 11:48 AM

Yeah. I understand. I contribute when I can. What I've learned here has made it possible for me to work in this industry. I'm very grateful for that.

And it's cool to be appreciated.

Richard