If you're going to go for a function, consider passing it the value (i.e. the 9), and have it count the values above that. You've alrage for the table...

Supplement Sergiy's great suggestion with something like a Tally Table, and you could write years worth of these at a time.

A good starting point would be here:

http://www.sqlservercentral.com/articles/TSQL/62867/%5B/url%5D

All right - back to reality for one post....

This was what I was using to test the performance I mentioned earlier. Essentially - I'm using a @sample to...

Jeff Moden (6/16/2008)

---

Matt Miller (6/16/2008)

---

You can try that,...

Careful - I was out getting coffee...:)

besides - it seems I spend a fair amount of time posting links to your articles anyway...:D

You can try that, but the invisible fence...

Pierre - you might care to take a look at this article. Neither one of those is going to be particularly efficient, since one is a WHILE loop, and...

rbarryyoung (6/16/2008)

---

I would be very interested if you could find a way to make that happen. I haven't tried it in Excel 2007, but in every other version that...

Tally table is basically the glue that makes it work. It's a utility table or sorts....

Take a look at the article. perfect good code on how to set...

This ought to do the trick ("real life example 2")

http://www.sqlservercentral.com/articles/Advanced/understandingobjectownership/1966/%5B/url%5D

select pk, count(*)

from

(SELECT lc_id pk,

...

I could swear that if you define a named range covering the query, it would update as the query does......

Mike Levan (6/16/2008)

---

thanks guys..

i cant change the design. I just want the result as i need and doesnt mind if am using temp table or any long tsql query.

I am...

Post your query if you get stuck - you should be able to get at those pretty definitively.

Your best bet will be to use something like the SPLIT described here...

http://www.sqlservercentral.com/articles/TSQL/62867/%5B/url%5D

(Edit: for what it's worth - it's the very article Michael is getting at).

That way you can easily...