How Can I generate sequence No in SQL Query?

Question

Id  Desc     Sequenceno
-----------------------------
84  TEST         1
84  TEST2        2
84  TEST3        3
85  IPhone       1
85  IPhone1      2
87  Nokia        1
88  Vivo         1
88  Vivo1        2

I have Id and Desc Column. How can I get Sequenceno Column like above?

Thanks in Advance.

What platform of DB are you using?

Circle Hsiao
– Circle Hsiao

2018-01-20 04:46:10 +00:00
Commented Jan 20, 2018 at 4:46 — Circle Hsiao
– Circle Hsiao, Commented Jan 20, 2018 at 4:46
Thanx Gordon to edit my question

Sagar padhiyar
– Sagar padhiyar

2018-01-20 04:48:36 +00:00
Commented Jan 20, 2018 at 4:48 — Sagar padhiyar
– Sagar padhiyar, Commented Jan 20, 2018 at 4:48
platform SQL Server

Sagar padhiyar
– Sagar padhiyar

2018-01-20 04:48:57 +00:00
Commented Jan 20, 2018 at 4:48 — Sagar padhiyar
– Sagar padhiyar, Commented Jan 20, 2018 at 4:48

Gordon Linoff · Accepted Answer · 2018-01-20 20:24:07Z

4

Use the ANSI-standard row_number() function:

select t.*, row_number() over (partition by id order by [desc]) as seqnum
from t;

edited Jan 20, 2018 at 20:24

answered Jan 20, 2018 at 4:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Sagar padhiyar Over a year ago

select t.*, row_number() over (partition by id order by id desc) as seqnum from t

Sagar padhiyar Over a year ago

Thanks for hint Gordon

Gordon Linoff Over a year ago

@Sagarpadhiyar . . . desc is the name of the column. Very bad name because it conflicts with a keyword. I fixed the answer.

Dhaval Asodariya · Accepted Answer · 2018-01-20 05:03:46Z

1

Try this:

SELECT
  Id, [Desc], 
  ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS Sequenceno
FROM #Temp;

answered Jan 20, 2018 at 5:03

Dhaval Asodariya

5585 silver badges19 bronze badges

Collectives™ on Stack Overflow

How Can I generate sequence No in SQL Query?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related