how to give row number only for consecutive combination

Question

I want to give row number only to the combination when it appears in a consecutive manner like , Please guide on how to get this type of sequence numbering in Tsql

I have tried with

ROW_NUMBER() Over (Partition by name,Order_type Order by REC_TS)
where record REC_TS is the time of order.



 Name:  Order type:     Seq Number:

    AAA     ONLINE  1

    AAA     ONLINE  2

    AAA     STORE   1

    AAA     SPOT    1

    AAA     STORE   1

    AAA     SPOT    1

    AAA     SPOT    2

    AAA    ONLINE   1

on which column are you ordering this data? is there another column like date? — ughai, Apr 23 '15 at 13:07
yes there is an another column as REC_TS which has date and time of order — Kaleem, Apr 23 '15 at 13:08
Forget the row number. What is ordering the results? A table has not inherent order. You need to have column and sort to order results. — paparazzo, Apr 23 '15 at 13:12
i want the Seq Number to be generated like the one given in the result set. How to query this table to get that result? — Kaleem, Apr 23 '15 at 13:16
how is `teradata` used here? please don't tag irrelevant products — ughai, Apr 23 '15 at 13:27
You can't. What part of a table has no inherent order is not clear? Those rows are not in consecutive manner. If you cannot add a sort to produce that order then you cannot produce those sequence numbers. — paparazzo, Apr 23 '15 at 13:31
@Kaleem - add your column `REC_TS` in the output as well to show the ordering of data — ughai, Apr 23 '15 at 13:37
@Blam - based on OP's comment he is using `REC_TS` to order the data which is missing from his output — ughai, Apr 23 '15 at 13:39
@ughai Exactly and that is why there is no "consecutive manner". Partition by name, Order_type will not have any of those broken up. — paparazzo, Apr 23 '15 at 13:54
@Blam - I believe what OP wants is when data is ordered based on `REC_TS`, if the previous row has the same name and order_type, sequence should be incremented by 1 else it should restart from 1. That is the basis on which i have framed my solution — ughai, Apr 23 '15 at 13:58

score 1 · Accepted Answer · answered Apr 23 '15 at 13:21

Use ROW_NUMBER() with CTE. The query is based that when there are consecutive values, then different between ROW_NUMBER () OVER (ORDER BY REC_TS) and ROW_NUMBER () OVER (PARTITION BY Name, OrderType ORDER BY REC_TS) will remain same.

Something like this

DECLARE @Table1 TABLE
(
    Name VARCHAR(10),
    OrderType VARCHAR(10),
    REC_TS DATETIME
)

Insert into @Table1
SELECT 'AAA','ONLINE','2014-09-01'
UNION ALL SELECT 'AAA','ONLINE','2014-09-02'
UNION ALL  SELECT 'AAA','STORE','2014-09-03' 
UNION ALL SELECT  'AAA','SPOT','2014-09-04'
UNION ALL SELECT  'AAA','STORE','2014-09-05'
UNION ALL SELECT  'AAA','SPOT','2014-09-06'
UNION ALL SELECT  'AAA','SPOT','2014-09-07'
UNION ALL SELECT  'AAA','ONLINE','2014-09-07'


;WITH CTE
AS (SELECT *, 
ROW_NUMBER () OVER (ORDER BY REC_TS) - ROW_NUMBER () OVER (PARTITION BY Name, 
            OrderType
            ORDER BY REC_TS) AS RowGroup
FROM @Table1) 
SELECT ROW_NUMBER () OVER (PARTITION BY RowGroup, 
        Name, 
        OrderType
        ORDER BY REC_TS) AS Sequence, 
Name,OrderType
FROM CTE
ORDER BY REC_TS;

Steve Ford · Answer 2 · 2015-04-23T14:29:14.840

Try this:

CREATE TABLE Orders 
(
    Name VARCHAR(10),
    OrderType VARCHAR(10),
    REC_TS DATETIME
)

Insert into Orders
VALUES 
  ('AAA','ONLINE','2014-09-01'),
  ('AAA','ONLINE','2014-09-02'),
  ('AAA','STORE','2014-09-03'), 
  ('AAA','SPOT','2014-09-04'),
  ('AAA','STORE','2014-09-05'),
  ('AAA','SPOT','2014-09-06'),
  ('AAA','SPOT','2014-09-07'),
  ('AAA','ONLINE','2014-09-07')

;WITH OrdersRN
AS
(
    SELECT [Name], OrderType, ROW_NUMBER() OVER (ORDER BY [REC_TS]) AS RN, [REC_TS]
    FROM Orders 
),
OrdersRecurs 
AS
(
    SELECT O1.Name, O1.OrderType, 1 As Seq, O1.RN
    FROM OrdersRN O1
    WHERE RN = 1

    UNION ALL
    SELECT O1.Name, O1.OrderType, 
        CASE
            WHEN Prev.Name IS NULL THEN 1 
            WHEN Prev.Name = O1.Name AND Prev.OrderType = O1.OrderType THEN Prev.Seq + 1
            ELSE 1
        END As Seq, 
        O1.RN
    FROM OrdersRN O1
    INNER JOIN OrdersRecurs Prev
        ON Prev.RN = O1.RN - 1
    WHERE O1.RN <> 1

)
SELECT O.[Name], O.OrderType, O.[Seq]
FROM OrdersRecurs O

how to give row number only for consecutive combination

2 Answers2