Add Seq Number at Query

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I want to add seq no using query and the seq no start from "0001" when Nomor_SJ is different.

How could I do it?

Thank you.
SELECT   Nomor_SJ,  ROW_NUMBER() OVER (ORDER BY Nomor_SJ) AS Row,  ProdukCode
FROM         TDSJPOJUAL2009

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
This should do:

And kindly confirm whether you need to convert Row as varchar with 0's appended before Row value like '0001'.
SELECT   Nomor_SJ,  ROW_NUMBER() OVER (partition by Nomor_SJ ORDER BY Nomor_SJ) AS Row,  ProdukCode
FROM         TDSJPOJUAL2009

Open in new window

HainKurtSr. System Analyst
Commented:
here formatted version...
SELECT Nomor_SJ,
       right('0000' + cast(row_number() over (partition by Nomor_SJ order by Nomor) as varchar),4) as Row 
       ProdukCode
  FROM TDSJPOJUAL2009

Open in new window

Author

Commented:
Hi All,

It works. I combine both query from you to get result.

Thank you very much for your help.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial