Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 01 Nov 2010 @ 18:23:17 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Need Same Order as in input table
 
From:   Singh, Lakhwant

Hello Guys,

I am working on some code where I need to add a sequence number with the input record (taken from a table A) and load it in table B. Both tables are SET-UPI.

For example: I have a "Table A" with following data:

     Field1 Field2 Field3 Field4
     1        2        3        5
     1        2        3        4

I want to load data from Table A to B (replica of A with an extra column added for Row_Number() function) and the result must be

     Row Nbr Field1 Field2 Field3 Field4
     1                  1        2        3        5
     2                  1        2        3        4

SQL is:

     Insert into Table-B
     (
             Row_nbr,
             ,Field1
             ,Field2
             ,Field3
             ,Field4
     )
     Select
             Row_number() Over (Order By Field1, Field2, Field3)
             ,Field1
             ,Field2
             ,Field3
             ,Field4
     From    Table-A;

During testing I loaded data from A to B several times but sometimes I got result-1 and get result-2:

Result-1

     Row Nbr         Field1 Field2 Field3 Field4
     1                  1        2        3        5
     2                  1        2        3        4

Result-2

     Row Nbr         Field1 Field2 Field3 Field4
     1                  1        2        3        4
     2                  1        2        3        5

Anyone has any idea why is it so that it is not picking the first record first always?

I always want result-1 i.e. the order should be same as in Table A.

Is there any way to do this?


Thanks

Lakhwant



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016