How to calculate Median for each group from a large set of data?

I know how to calculate Median from Ken's book 'The Guru's Guide to Transact-SQL', but the example given in the book only deals with one set (group) of numbers. In my case, I need to group by each code and before group by I have a total of 18 million records so using cross join (one way talked about in the book to get Median) might take a long time. If using Identity, i wonder how to insert id which starts 1 again when a new group starts. Thanks!
qinyanAsked:
Who is Participating?
 
kenhaleyConnect With a Mentor Commented:
Identity won't work if the numbers need to start over on each group, so you'll need to populate the new id column yourself.  If you're using SQL 2005, look at the functions RANK, ROW_NUMBER, and NTILE, (aka, "ranking functions") which are designed for problems like this.  If not, you'll have to go ahead and write your own code to create the ID column and populate it yourself.  I'm thinking a cursor would be the most straightforward way, but with 18 million rows, that could take awhile.  But at least that way you have full control over exactly how the id gets assigned.
0
 
qinyanAuthor Commented:
I'm using SQL 2005 but it says Rank / Row_Number is not a recognized function name. How could that be? Thanks!
0
 
kenhaleyConnect With a Mentor Commented:
It's possible that you have SQL 2005 client utilities but that the back end is SQL 2000.  Or I suppose it could be a database running in SQL 2000 compatibility mode (if that's possible).  Anyway, those are things to check.
0
 
qinyanAuthor Commented:
I tested on a db running on SQL 2005 and it worked. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.