Have a table HDR with columns

ord_no   line_seq_no  comm_cd
  123        1          Z
  123        2          Y
  123        3          X
  222        1          C
  222        2          B
  222        3          A

I would like to create a view and add an extra column that will re sequence the lines by comm_cd. So I would get the following result.

ord_no   line_seq_no  comm_cd  New_Seq
  123        1          Z         3
  123        2          Y         2
  123        3          X         1
  222        1          C         3
  222        2          B         2
  222        3          A         1

SELECT ROW_NUMBER() OVER (PARTITION BY ord_no ORDER BY comm_cd) AS New_Seq,*
FROM yourtable