Rolling down data based on key column

  • 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.

  • 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!

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply