I am trying to insert record into temp table with ordered seq no as one column and based on order of particular field values . I am tried using the following query :
INSERT into temp_table
SUM(1) OVER(ROWS UNBOUNDED PRECEDING) + 0 AS REC_SEQ
order by column1,column2, column3;
I got syntax error like order by clause cannot be used in subquery.
Can anyone help to get the logic to implement the above req.
Thanks in advance.......
Try with row_number :
insert into temp_table select column1 , row_number() over(order by column1, column2, column3) as seq_no from base_table;
but i used the order function as you share before like
SyntaxEditor Code Snippet
insert into temp table
select incident_no, Incident_date, Task_Date, Task_time,
,row_number() over (ORDER BY MIN(Task_Date) OVER(PARTITION BY Primry_incident_no), Primry_incident_no, Task_Date, Task_time)
so getting error while using along with row_number as
Failed 5480 : ordered analytical fucntion can not be nested
You have to go for a two-steps stat query :
insert into temp_table with cte_orig_task_date as ( select incident_no, Incident_date, Task_Date, Task_time, Primry_incident_no , min(Task_Date) over(partition by Primry_incident_no) as orig_Task_Date from MyTable ) select incident_no, Incident_date, Task_Date, Task_time , row_number() over(/*partition by Primry_incident_no*/ order by orig_Task_Date, Primry_incident_no, Task_Date, Task_time) as seq_no from cte_orig_task_date;
I'm not sure if you need a partition by within the row_number, so I let it as a comment for now.