none
Get first occurance of records in a group

    Question

  • Hello,

    I have a query that I'm trying to get the first occurance of each item grouping when an item changes.  However, the is grouping all similar items together. For example;

    Select LocationNumber, ShelfNumber, item, recvDate,

    ROW_NUMBER() over (partition by item order by LocationNumber, ShelfNumber, recvDate) as roworder

    The query returns something like:

    LocationNumber, ShelfNumber, item, recvDate, Roworder

    1                         1                 123   01/6/14 16:38:00        3

    1                         1                 123   01/04/14 05:35:15      2

    1                         1                 456   01/03/14 07:18:23       1

    1                         1                 123   01/01/14 08:25:15      1

    When I grab all the row_numbers  = 1 this will give me 2 records (should be 3). 

    The query needs have the row_number start over whenever an item changes.  In this case there should be 3 records returned.  How would I do this? for example:

    LocationNumber, ShelfNumber, item, recvDate, Roworder

    1                         1                 123   01/6/14 16:38:00      2

    1                         1                 123   01/04/14 05:35:15       1

    1                         1                 456   01/03/14 07:18:23       1

    1                         1                 123   01/01/14 08:25:15       1

    Thanks in advance!

    Thursday, January 09, 2014 11:28 PM

Answers

  • This is a "gaps and islands" problem.  You can google that term for lots of good info about this type of problem and how to solve them.  One way for your problem would be:

    Declare @SampleTable Table (LocationNumber int, ShelfNumber int, item int, recvDate datetime);
    Insert @SampleTable(LocationNumber, ShelfNumber, item, recvDate) Values
    (1,                         1,                 123,   '01/6/14 16:38:00'),
    (1,                         1,                 123,   '01/04/14 05:35:15'),
    (1,                         1,                 456,   '01/03/14 07:18:23'),
    (1,                         1,                 123,   '01/01/14 08:25:15');
    
    ;With cte As
    (Select LocationNumber, ShelfNumber, item, recvDate,
      Row_Number() Over (Partition By LocationNumber, ShelfNumber Order By recvDate)
        - Row_Number() Over (Partition By LocationNumber, ShelfNumber, item Order By recvDate) As Island
    From @SampleTable)
    Select LocationNumber, ShelfNumber, item, Min(recvDate) As recvDate
    From cte
    Group By LocationNumber, ShelfNumber, item, Island;

    Tom

    Thursday, January 09, 2014 11:46 PM