Rolling down data based on key column

• Sachin Vaidya

  Ten Centuries

  Points: 1273

  More actions

  April 18, 2011 at 5:32 am

  #96198

  Hello,

  I have a key column that has values start from 1 to 9. I create groups of those keys such that all key values starting from 1 belong to group id 1, all key values starting from 2 belong to group id 2 and so on.

  Some of these groups have huge no. of records, so now for such groups I want to further roll down the key values and create new groups such that, for e.g., if group id 5 has huge no. of records then I want to break it to separate key values starting from 50 in different group, 51 in different group, 52 in different group and so on.

  Not able to put up a proper logic and looking for some help for this.

  Regards.

• Lowell

  SSC Guru

  Points: 323527

  More actions

  April 18, 2011 at 7:13 am

  #1312730

  really need a bit more of the table definition to give you some solid code examples.

  basically, what you want is to use a tally table to help generate all possible values, and then a WHERE statement to determine which items get the grouping.

  show us the table so we can give a coherent example.

  my other question is to try and understand the premise: why do you need to sub group the data? what is wrong with a table or group having 100 records or a million?

  Data is data, you can always use a method to present it in a different manner if needed, whether row_number or something.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Sachin Vaidya

  Ten Centuries

  Points: 1273

  More actions

  April 19, 2011 at 12:44 am

  #1313188

  Thanks!!

  Sure, I can give you more details.

  let's assume I have column named key with values, 1001,1002,1101,1201, 3001,3201,3202,3301, 5001,5201,5202,5203 and so on.

  now I group the data for these key values and put it in other tables such that rows with key values starting from 1 are placed in separate table, similarly rows starting from 3 are placed in separate tables and so on.

  Now if suppose there are millions and millions of rows that have key value starting from 3 and I want to reduce the table size, so I will check if no. of rows starting with '30' are more (I have to decide a threshold no.) then place these rows in a separate table.

  This is what I am looking for. I hope I have explained the situation better now?

  Please let me know if you need further explanation.

  Regards.