[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

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!
0
qinyan
Asked:
qinyan
  • 2
  • 2
2 Solutions
 
kenhaleyCommented:
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
 
kenhaleyCommented:
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now