Post reply

Creating Field to group records

  • July 15, 2010 at 9:50 am

    #387363

    I have some records for some cards that need to be printed. These cards need to go into boxes of 50 so my data needs to have a field saying which box they go into. So first 50 rows have a field with 1 in, second 50 says 2, etc.

    Is there a way of doing this ?

  • July 15, 2010 at 4:10 pm

    #1193816

    I'd rather use

    SELECT vague_stuff,

    ((ROW_NUMBER() OVER (ORDER BY something)-1) / 50 + 1 ) AS box_nbr

    FROM NamelessFoobar;

    Otherwise your first box would only contain 49 cards...

    The reason is that ROW_NUMBER() start with 1 so you need to make sure the 50th card will still belong to the first box. When using te original code, you'd end up with 50/50 + 1 = 2. Therefore, you'd need to apply an offset of -1.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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