creating a sequence of number starting or each group of ID

• hegdesuchi

  Hall of Fame

  Points: 3047

  More actions

  October 30, 2016 at 6:45 am

  #314451

  I have 3 source table and have to union them all source 1:

  Id name car

  1 aaa BMW

  1 aaa Porche

  1 aaa Nissan

  2 bbb Nissan

  2 bbb Honda

  source 2:

  Id name car

  1 aaa Camry

  source 3:

  Id name car

  1 aaa Honda

  1 aaa Ford

  2 bbb Buick

  Now I want to combine them all and insert into a table with a sequence. this sequence will have incremental Value repeating for each ID.

  Target table

  Id name car sequence

  1 aaa BMW 1

  1 aaa Porche 2

  1 aaa Nissan 3

  1 aaa Camry 4

  1 aaa Honda 5

  1 aaa Ford 6

  2 bbb Nissan 1

  2 bbb Honda 2

  2 bbb Buick 3

  I know i have to use row_number() and partition by but unable to capture the sequence with Union with this function. How ever, I can insert them in temp table and create another temp table with row_number, it works fine. I unable to update the column with row_number() as well.I wanted to know if there is direct approach with union or if I can update the target table?

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  October 30, 2016 at 6:53 am

  #1909510

  Quick suggestion, do the union in a CTE and apply the row_number outside the CTE

  😎

• hegdesuchi

  Hall of Fame

  Points: 3047

  More actions

  October 31, 2016 at 9:53 am

  #1909678

  thank you :-). I put them in CTE and it worked