Post reply

Batch selecy query result data to 50

  • October 6, 2009 at 3:53 am

    #134282

    Hi,

    To whom it may concern:

    I have an issue regarding batching a rows/records by 50.

    Im using sql server 2005 and jasper report for reporting.

    The scenarios are these, I want to create a select query that batch rows by 50, we can make a column that identify that these rows are batch 1,batch-2,batch-3 and so on that depends of the number of rows return.

    for example results:

    column1 column2

    value1 batch-1

    value2 batch-1

    value3 batch-1

    ....

    value50 batch-2

    value51 batch-2

    ....

    and so on.

    And i need these column2 to group chart data according to their batch, which means that every page contain 50 data.

    Hope you understand my question.

    Thanks and regards.

  • October 6, 2009 at 4:33 am

    #1062160

    use something like

    row_number() over (order by column1 ) /50


    Clear Sky SQL
    My Blog[/url]

  • October 6, 2009 at 7:48 pm

    #1062563

    Thanks Dave,

    Simple but good solution. Now I need to do a manuever to this thing to make an exact set of numbers by 50.

    By the way thanks again.

  • October 6, 2009 at 10:38 pm

    #1062575

    Hey Dave,

    I have a followup question.

    How to create a parent group and subgroup of data still using with row_number() over() function.

    The use of parent group is to group the child/sub group.

    Example resuts:

    Column1 Column2(rowid)

    Group1 1

    Group1 2

    Group1 3

    ..... ..

    Group2 1

    Group2 2

    Group2 3

    .... ...

    Hope you understand my question.

    Thanks.

  • October 7, 2009 at 1:22 am

    #1062612

    You need to use partition by

    row_number() over (partition by column1 order by .....)


    Clear Sky SQL
    My Blog[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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