Search code examples
sqlranking

SQL ranking repeating groups

I have the following data which is sorted by start date. What i want to do is rank the product name as is appears for example product number 27675 is on row 1 and the first time this product is found therefore rank 1... again this product is on row 5 so rank 2.

I want to then select this data without the date and group by section, row, product and rank.

SECTION_NAME    ROW_NAME    PRODUCT NAME    start_date         DESIRED RANK    
SEW             LS-1-A_1    27675           02/03/2016 14:39    1
SEW             LS-1-A_1    26298           06/04/2016 16:56    1
SEW             LS-1-A_1    26298           16/05/2016 16:56    1
SEW             LS-1-A_1    26298           04/06/2016 09:26    1
SEW             LS-1-A_1    27675           16/06/2016 16:22    2
SEW             LS-1-A_1    18969           17/07/2016 12:54    1
SEW             LS-1-A_1    26298           01/08/2016 10:52    2
SEW             LS-1-A_1    27675           29/08/2016 08:38    3
SEW             LS-1-A_1    LEVI F17 SMS    12/09/2016 13:39    1

the rank i am producing is:

SECTION_NAME    ROW_NAME    PRODUCT NAME    start_date             RANK    
SEW             LS-1-A_1    27675           02/03/2016 14:39    1
SEW             LS-1-A_1    26298           06/04/2016 16:56    1
SEW             LS-1-A_1    26298           16/05/2016 16:56    2
SEW             LS-1-A_1    26298           04/06/2016 09:26    3
SEW             LS-1-A_1    27675           16/06/2016 16:22    2
SEW             LS-1-A_1    18969           17/07/2016 12:54    1
SEW             LS-1-A_1    26298           01/08/2016 10:52    4
SEW             LS-1-A_1    27675           29/08/2016 08:38    3
SEW             LS-1-A_1    LEVI F17 SMS    12/09/2016 13:39    1


SELECT 
   SECTION_NAME,
   ROW_NAME,
   PRODUCT_NAME,
   start_date, 
   dense_rank() OVER (PARTITION BY product_name ORDER BY START_DATE)RANK
FROM
   TABLES

Order by
    SECTION_NAME,ROW_NAME,START_DATE

UPDATE----

using the following:

SELECT 
  SECTION_NAME ,
  ROW_NAME,
  PRODUCT_NAME [Style], 
  (START_DATE) as [Start dt],
  SUM(isChange) OVER (PARTITION BY SECTION_NAME , ROW_NAME,product_name     ORDER BY START_DATE) as rank_

FROM (SELECT SECTION_NAME,
        ROW_NAME,
        PRODUCT_NAME,
        sd.START_DATE,
         (CASE WHEN lag(PR.ROW_NAME ) over (order by SD.START_DATE) = PR.ROW_NAME 
               THEN 0
               ELSE 1
          END) as IsChange
  FROM 
    TABLES_t
 ) t
Order by SECTION_NAME, ROW_NAME, START_DATE;

Produces this: ( In situations where the product is the same on each row (10,11,12) i would expect these to have the same rank.

enter image description here

Solution

  • Needed to correct the ordering in the partition by.

    SELECT 
SECTION_NAME ,
ROW_NAME,
PRODUCT_NAME [Style], 
(START_DATE) ,
SUM(isChange) OVER (PARTITION BY ROW_NAME,product_name ORDER BY START_DATE) as rank_
,IsChange
FROM (SELECT SECTION_NAME,
        ROW_NAME,
        PRODUCT_NAME,
        sd.START_DATE,
         (CASE WHEN lag(product_name ) over (order by SECTION_NAME, ROW_NAME, sd.START_DATE) = product_name 
               THEN 0
               ELSE 1
          END) as IsChange


  FROM 
    Table_ t
 ) t

Order by SECTION_NAME, ROW_NAME, START_DATE;

    enter image description here