How to create Sequence No for sub item by using SQL?

Question

Below is a table in databasse.

A is main item . aas,asd and ads is sub item of A

B is main item . byw and bmw is sub item of A

  ____Main Item_____|________Sub Item_______
          A         |          aas                             
          A         |          asd
          A         |          ads
          B         |          byw
          B         |          bmw

I need to display like these. It will added SEQ_NO column..

  ____Main Item_____|_____SEQ_NO___|____Sub Item_______
          A         |       1      |      aas                             
          A         |       2      |      asd
          A         |       3      |      ads
          B         |       1      |      byw
          B         |       2      |      bmw

I use sql server.

How is this ordered? How come asd comes after aas, but before ads? — Mureinik
– Mureinik, Commented Sep 21, 2015 at 22:57

Vamsi Prabhala · Accepted Answer · 2015-09-21 22:58:17Z

3

You can use row_number function to generate the seq_no column. Also change the sub_item ordering as per your requirements.

select main_item,
row_number() over(partition by main_item order by sub_item) as seq_no,
sub_item 
from tablename

answered Sep 21, 2015 at 22:58

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

How to create Sequence No for sub item by using SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related