teradata sql query to assign same rownumbers

Question

How to assign same rownumber to group of sequence records. Refer the below example orderno.s 550 &650 are having two sets of running sequence numbers I wanted to assign same row number for each corresponding ordernumber and set of sequence numbers(1,2,3)

Order,   Item,  SequenceNumber
550,     AA,      1
550,     AA,      2
550,     AA,      3

550,     AA,      1
550,     AA,      2
550,     AA,      3
550,     AA,      4

650,     AA,      1
650,     AA,      2
650,     AA,      3

650,     AA,      1
650,     AA,      2
650,     AA,      3
650,     AA,      4

Expected result should be with the new rownumber column populated as below.

Order,    Item,  SequenceNumber ,    Rownumber
550,      AA,      1,                     1
550,      AA,      2,                     1
550,      AA,      3,                     1

550,      AA,      1,                     2
550,      AA,      2,                     2
550,      AA,      3,                     2
550,      AA,      4,                     2

650,      AA,      1,                     1
650,      AA,      2,                     1
650,      AA,      3,                     1

650,      AA,      1,                     2
650,      AA,      2,                     2
650,      AA,      3,                     2
650,      AA,      4,                     2

thanks dnoeth, your solution worked for me...

geo
– geo

2017-05-13 07:44:45 +00:00
Commented May 13, 2017 at 7:44 — geo
– geo, Commented May 13, 2017 at 7:44

dnoeth · Accepted Answer · 2017-05-12 18:15:06Z

2

You need nested OLAP functions, but of course there must be some column to order your data:

SELECT ...
   Sum(flag) -- create the group number
   Over (PARTITION BY Order, Item
                   ORDER BY whatever
                   ROWS Unbounded Preceding)
FROM
 (
   SELECT ...
      -- find the row where a new group starts
      CASE WHEN Min(SequenceNumber) 
                Over (PARTITION BY Order, Item
                      ORDER BY whatever
                      ROWS BETWEEN 1 Preceding AND 1 Preceding) + 1 = SequenceNumber
           THEN 0
           ELSE 1
      END AS flag
   FROM tab
 ) AS dt

answered May 12, 2017 at 18:15

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Z. Bagley · Accepted Answer · 2017-05-12 17:22:20Z

0

From your existing table, you need to create an ALTER statement so that you can add new column.

ALTER TABLE mytableName ADD Rownumber INT;

After the statement has been successfully executed, you can now update all the records,

UPDATE myTableName SET Rownumber = (some logic statement depending on table values)

edit: I'd be happy to help with that (some logic statement...), but I'm not exactly sure what you're looking for from your question.

answered May 12, 2017 at 17:22

Z. Bagley

9,2781 gold badge44 silver badges55 bronze badges

4 Comments

geo Over a year ago

I'm not concerned about adding the column, I'm looking for logic to populate the rownumber column (I mean assigning same rownumber to each set of running sequence and orderno.

geo Over a year ago

Can you please explain what the below code does... Sum(flag) -- create the group number Over (PARTITION BY Order, Item ORDER BY whatever ROWS Unbounded Preceding)

geo Over a year ago

I'm not really sure how Sum(flag) creates new group number..in above case..

Z. Bagley Over a year ago

Might want to add those comments to the other question so he gets notified. From that first comment I understand what you're looking to do now, but he has the right answer for you.

Community · Accepted Answer · 2017-05-23 11:54:46Z

Here's the SQL:

SELECT
  OrderNumber
, ItemCode
, SequenceNumber
, ROW_NUMBER() OVER(PARTITION BY OrderNumber, SequenceNumber, SequenceNumberCount) AS RowNumber
FROM
(
    SELECT
      OrderNumber
    , ItemCode
    , SequenceNumber
    , COUNT(SequenceNumber) OVER(PARTITION BY OrderNumber, SequenceNumber ORDER BY OrderNumber, ItemCode) AS SequenceNumberCount
    FROM
    (
        SELECT 550 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
        SELECT 550 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
        SELECT 550 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
        SELECT 550 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
        SELECT 550 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
        SELECT 550 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
        SELECT 550 AS OrderNumber, 'AA' As ItemCode, 4 AS SequenceNumber UNION ALL
        SELECT 650 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
        SELECT 650 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
        SELECT 650 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
        SELECT 650 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
        SELECT 650 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
        SELECT 650 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
        SELECT 650 AS OrderNumber, 'AA' As ItemCode, 4 AS SequenceNumber 
    ) AS data
) AS interim_calculation
ORDER BY
  OrderNumber
, RowNumber
, SequenceNumber;

Update:

Updated SQL to remove redundant complexity based on @dnoeth's comment:

SELECT
  OrderNumber
, ItemCode
, SequenceNumber
, Row_Number() Over(PARTITION BY OrderNumber, SequenceNumber ORDER BY SequenceNumber) AS RowNumber
FROM
(
    SELECT 550 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
    SELECT 550 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
    SELECT 550 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
    SELECT 550 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
    SELECT 550 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
    SELECT 550 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
    SELECT 550 AS OrderNumber, 'AA' As ItemCode, 4 AS SequenceNumber UNION ALL
    SELECT 650 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
    SELECT 650 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
    SELECT 650 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
    SELECT 650 AS OrderNumber, 'AA' As ItemCode, 1 AS SequenceNumber UNION ALL
    SELECT 650 AS OrderNumber, 'AA' As ItemCode, 2 AS SequenceNumber UNION ALL
    SELECT 650 AS OrderNumber, 'AA' As ItemCode, 3 AS SequenceNumber UNION ALL
    SELECT 650 AS OrderNumber, 'AA' As ItemCode, 4 AS SequenceNumber 
) AS data
ORDER BY
  OrderNumber
, RowNumber
, SequenceNumber;

which results in:

That's way too complicated: Row_Number() Over(PARTITION BY OrderNumber, SequenceNumber ORDER BY SequenceNumber) AS RowNumber returns the same without nesting :-)

Collectives™ on Stack Overflow

teradata sql query to assign same rownumbers

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related