CODEWITHSUNDEEP
sqlranking
Chris
Chris

Reputation: 175

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

Upvotes: 0

Views: 523

Answers (2)

Chris
Chris

Reputation: 175

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is actually similar to your other question. The difference is that the counting needs to start over again for each product_name. So, here is one method:

SELECT PS.SECTION_NAME [Planning Group], PR.ROW_NAME as [Planning Row],
       P.PRODUCT_NAME [Style], (SD.START_DATE) as [Start dt],
       SUM(isChange) OVER (PARTITION BY product_name ORDER BY start_dt) as rank_
FROM (SELECT t.*,
             (CASE WHEN lag(row) over (order by start_dt) = row
                   THEN 0
                   ELSE 1
              END) as IsChange
      FROM tables_ t
     ) t
Order by PS.SECTION_NAME, PR.ROW_NAME, SD.START_DATE;

Note: in this question as in the previous one, I don't understand the role of SECTION_NAME and ROW_NAME. This version ignores these columns. If you want the query to restart the numbering in each group, then include them in the partition by statement.

Upvotes: 2

Related Questions