assigning sequence number in sql query

Question

I have scenario, where I have to give sequence number for each record. And the sequence number should re-initialized to initial value if it is different account.

I was trying like this

declare @account_no varchar(50)
declare @seq_no int
declare @temp_no int
set @temp_no=1

select 
    (@account_no = (CASE WHEN @account_no <> PN.vst_int_id 
                           THEN PN.vst_int_id
                    END))
    ,(@seq_no = (CASE WHEN @account_no = PN.vst_int_id 
                        THEN @temp_no + 1
                      ELSE 1
                 END))
   ,@seq_no
   ,nte_pri_cd 
from 
   TSM310_PATIENT_NOTES PN
where 
   vst_int_id = '4588611'

M.Ali · Accepted Answer · 2014-05-06 18:58:43Z

6

Good news we have Ranking Functions in sql server to make these kind of operations very simple. Note it is only available in Sql Server 2005 and later editions.

select *,
       ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY SomeColumn) SequenceNumber

from TSM310_PATIENT_NOTES PN

answered May 6, 2014 at 18:58

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

2 Comments

Ravi Over a year ago

Thanks.. that's what I was looking for. I will get back to you if I will get any issue

M.Ali Over a year ago

Not a problem glad to help :)

Collectives™ on Stack Overflow

assigning sequence number in sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related