Post reply

Help with Group ID Every X rows query

  • August 2, 2013 at 4:28 am

    #395238

    Hi! I have a query where I want a group id every x rows.

    Here is some sample data of what I'm working on:

    create table #temp (

    reference nvarchar(10),

    collection_day nvarchar(50),

    sort_date datetime,

    )

    insert into #temp (reference, collection_day, sort_date)

    select '02015','Friday 7th December 2012','2012-12-07 09:00:00.000' union all

    select '02015','Friday 7th December 2012','2012-12-07 11:00:00.000' union all

    select '02015','Friday 7th December 2012','2012-12-07 13:00:00.000' union all

    select '02015','Friday 7th December 2012','2012-12-07 15:00:00.000' union all

    select '02015','Friday 7th December 2012','2012-12-07 17:00:00.000' union all

    select '02015','Friday 7th December 2012','2012-12-07 19:00:00.000' union all

    select '02015','Friday 7th December 2012','2012-12-07 21:00:00.000' union all

    select '02015','Saturday 8th December 2012','2012-12-08 09:00:00.000' union all

    select '02015','Saturday 8th December 2012','2012-12-08 11:00:00.000' union all

    select '02015','Saturday 8th December 2012','2012-12-08 13:00:00.000' union all

    select '02015','Saturday 8th December 2012','2012-12-08 15:00:00.000' union all

    select '02015','Saturday 8th December 2012','2012-12-08 17:00:00.000' union all

    select '02015','Saturday 8th December 2012','2012-12-08 19:00:00.000' union all

    select '02015','Saturday 8th December 2012','2012-12-08 21:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 09:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 11:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 11:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 12:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 13:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 13:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 13:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 14:00:00.000' union all

    select '02015','Sunday 5th May 2013','2013-05-05 14:00:00.000' union all

    select '02016','Monday 6th May 2013','2013-05-06 09:00:00.000' union all

    select '02016','Monday 6th May 2013','2013-05-06 11:00:00.000' union all

    select '02016','Monday 6th May 2013','2013-05-06 13:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 08:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 08:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 10:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 10:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 12:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 12:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 14:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 16:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 16:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 18:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 18:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 20:00:00.000' union all

    select '02016','Tuesday 7th May 2013','2013-05-07 20:00:00.000'

    select

    row_number() OVER (PARTITION BY reference, collection_day ORDER BY sort_date) AS rnum

    ,collection_day, reference, sort_date

    from #temp

    order by reference, sort_date

    So the output here is:

    rnumcollection_dayreferencesort_date

    1Friday 7th December 2012020152012-12-07 09:00:00.000

    2Friday 7th December 2012020152012-12-07 11:00:00.000

    3Friday 7th December 2012020152012-12-07 13:00:00.000

    4Friday 7th December 2012020152012-12-07 15:00:00.000

    5Friday 7th December 2012020152012-12-07 17:00:00.000

    6Friday 7th December 2012020152012-12-07 19:00:00.000

    7Friday 7th December 2012020152012-12-07 21:00:00.000

    1Saturday 8th December 2012020152012-12-08 09:00:00.000

    2Saturday 8th December 2012020152012-12-08 11:00:00.000

    3Saturday 8th December 2012020152012-12-08 13:00:00.000

    4Saturday 8th December 2012020152012-12-08 15:00:00.000

    5Saturday 8th December 2012020152012-12-08 17:00:00.000

    6Saturday 8th December 2012020152012-12-08 19:00:00.000

    7Saturday 8th December 2012020152012-12-08 21:00:00.000

    1Sunday 5th May 2013020152013-05-05 09:00:00.000

    2Sunday 5th May 2013020152013-05-05 11:00:00.000

    3Sunday 5th May 2013020152013-05-05 11:00:00.000

    4Sunday 5th May 2013020152013-05-05 12:00:00.000

    5Sunday 5th May 2013020152013-05-05 13:00:00.000

    6Sunday 5th May 2013020152013-05-05 13:00:00.000

    7Sunday 5th May 2013020152013-05-05 13:00:00.000

    8Sunday 5th May 2013020152013-05-05 14:00:00.000

    9Sunday 5th May 2013020152013-05-05 14:00:00.000

    1Monday 6th May 2013020162013-05-06 09:00:00.000

    2Monday 6th May 2013020162013-05-06 11:00:00.000

    3Monday 6th May 2013020162013-05-06 13:00:00.000

    1Tuesday 7th May 2013020162013-05-07 08:00:00.000

    2Tuesday 7th May 2013020162013-05-07 08:00:00.000

    3Tuesday 7th May 2013020162013-05-07 10:00:00.000

    4Tuesday 7th May 2013020162013-05-07 10:00:00.000

    5Tuesday 7th May 2013020162013-05-07 12:00:00.000

    6Tuesday 7th May 2013020162013-05-07 12:00:00.000

    7Tuesday 7th May 2013020162013-05-07 14:00:00.000

    8Tuesday 7th May 2013020162013-05-07 16:00:00.000

    9Tuesday 7th May 2013020162013-05-07 16:00:00.000

    10Tuesday 7th May 2013020162013-05-07 18:00:00.000

    11Tuesday 7th May 2013020162013-05-07 18:00:00.000

    12Tuesday 7th May 2013020162013-05-07 20:00:00.000

    13Tuesday 7th May 2013020162013-05-07 20:00:00.000

    I want a new group id (incremental for the whole set) every 6 rows with a collection day, or obviously where the collection day or the reference changes

    i.e.

    groupidrnumcollection_dayreferencesort_date

    11Friday 7th December 2012020152012-12-07 09:00:00.000

    12Friday 7th December 2012020152012-12-07 11:00:00.000

    13Friday 7th December 2012020152012-12-07 13:00:00.000

    14Friday 7th December 2012020152012-12-07 15:00:00.000

    15Friday 7th December 2012020152012-12-07 17:00:00.000

    16Friday 7th December 2012020152012-12-07 19:00:00.000

    27Friday 7th December 2012020152012-12-07 21:00:00.000

    31Saturday 8th December 2012020152012-12-08 09:00:00.000

    32Saturday 8th December 2012020152012-12-08 11:00:00.000

    33Saturday 8th December 2012020152012-12-08 13:00:00.000

    34Saturday 8th December 2012020152012-12-08 15:00:00.000

    35Saturday 8th December 2012020152012-12-08 17:00:00.000

    36Saturday 8th December 2012020152012-12-08 19:00:00.000

    47Saturday 8th December 2012020152012-12-08 21:00:00.000

    51Sunday 5th May 2013020152013-05-05 09:00:00.000

    52Sunday 5th May 2013020152013-05-05 11:00:00.000

    53Sunday 5th May 2013020152013-05-05 11:00:00.000

    54Sunday 5th May 2013020152013-05-05 12:00:00.000

    55Sunday 5th May 2013020152013-05-05 13:00:00.000

    56Sunday 5th May 2013020152013-05-05 13:00:00.000

    67Sunday 5th May 2013020152013-05-05 13:00:00.000

    68Sunday 5th May 2013020152013-05-05 14:00:00.000

    69Sunday 5th May 2013020152013-05-05 14:00:00.000

    71Monday 6th May 2013020162013-05-06 09:00:00.000

    72Monday 6th May 2013020162013-05-06 11:00:00.000

    73Monday 6th May 2013020162013-05-06 13:00:00.000

    81Tuesday 7th May 2013020162013-05-07 08:00:00.000

    82Tuesday 7th May 2013020162013-05-07 08:00:00.000

    83Tuesday 7th May 2013020162013-05-07 10:00:00.000

    84Tuesday 7th May 2013020162013-05-07 10:00:00.000

    85Tuesday 7th May 2013020162013-05-07 12:00:00.000

    86Tuesday 7th May 2013020162013-05-07 12:00:00.000

    97Tuesday 7th May 2013020162013-05-07 14:00:00.000

    98Tuesday 7th May 2013020162013-05-07 16:00:00.000

    99Tuesday 7th May 2013020162013-05-07 16:00:00.000

    910Tuesday 7th May 2013020162013-05-07 18:00:00.000

    911Tuesday 7th May 2013020162013-05-07 18:00:00.000

    912Tuesday 7th May 2013020162013-05-07 20:00:00.000

    1013Tuesday 7th May 2013020162013-05-07 20:00:00.000

    I can do it with a cursor but I'm sure there must be a good way to use a rank function to achieve this..

    Any help would be much appreciated! 🙂

  • August 2, 2013 at 4:40 am

    #1638115

    SELECT

    DENSE_RANK() OVER(ORDER BY reference, collection_day, (rnum-1)/6 ),

    *

    FROM (

    select

    row_number() OVER (PARTITION BY reference, collection_day ORDER BY sort_date) AS rnum

    ,collection_day, reference, sort_date

    from #temp

    ) d

    order by reference, sort_date

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 2, 2013 at 4:48 am

    #1638116

    Perfect, thank you! Thought it might be something like that but couldn't get it right.

    Cheers!

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

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