Thanks madhivanan

Hi

how would i use your statement select rownumber() over(partition by item order by item) as rownumber, * from @t in an update query to set the sequence number field in an existing table

i tried this update CAIUPDATEAP set TranSequence = (select rownumber() over(partition by trannum order by Trannum) as rownumber, * from CAIUPDATE_AP) but got an error Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thanks this is EXACTLY what i was looking for

Vic

vmanfredi, You need to try this

update T set T.TranSequence =S.TranSequence FROM TranSequence as T inner join 
(select rownumber() over(partition by trannum order by Trannum) as rownumber, * from CAIUPDATE_AP)  as T
on S.uniqu_col=T.unique_col

Hi

I have another related issue to this your solution to my first question worked perfrctly. I need to write an update statement that looks in table A finds the highest TranSeq number then take that number increase it by one then update table B transeq with that incremented number. So in the example below table B transeq needs to be updated to 3. This need to be dynamic as well, there could be more than one record in table b so I need to take that into account and keep incrementing and uptading table b until the next trannum,

Any ideas?

Table A TranNum TranSeq 123 0 123 1

Table B TranNum TranSeq 123 1

vmanfredi, can you post some sample data with expected result?

Sure

this is the data before
Table A
TranNum            TranSeq
123                    0
123                    1
456                     100
456                     101
Table B
TranNum              TranSeq
123                    1
456                       1
456                       1   
here is the result

I need to update the tran seq here in table B to be the next increment from table A taking multiple records for each tran num into account
Table B
TranNum               TranSeq
123                       2
456                        103
456                        104

thanks!!!!

See if you get correct result

select t1.TranNum,t1.sno+t2.sno-1 from
(
select TranNum , max(tranSeq)+row_number() over (partition by TranNum order by TranNum) as sno from TableA 
group by TranNum 
) as t1 inner join
(
select TranNum , row_number() over (partition by TranNum order by TranNum) as sno from TableB 
) as t2
on t1.TranNum =t2.TranNum

That worked perfectly. thank so much.I now have another issue here is the sample data.

How would I return JUST 456 and abc in a query result using tran_seq as the criteria.
tran_num tran_seq
123            0
123            1
123            2
456            0
789            0
789            1
abc            0

If you want to return TranNum that has only 0 as Tran_seq, try this

select TranNum from
(
select t1.TranNum,t1.sno+t2.sno-1 as Sno from
(
select TranNum , max(tranSeq)+row_number() over (partition by TranNum order by TranNum) as sno from TableA 
group by TranNum 
) as t1 inner join
(
select TranNum , row_number() over (partition by TranNum order by TranNum) as sno from TableB 
) as t2
on t1.TranNum =t2.TranNum
) as t2
group by TranNum
Having min(sno)=max(Sno) and Min(Sno)=0

hi thanks for that but I am only using a single table in this senario. The answer you gave before worked perfectly an I thank you for that. I dont need to increment the seq here just return the tran num that has ONLY a tran seq of 0.

If it is a single table, use

select TranNum from table
Group by TranNum 
Having min(Tran_num)=max(Tran_num) and min(Tran_num)=0

i got this error Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value ' FEW' to data type int. Not all the tran nums are numbers. Forgot to mention that

It should be tran_seq, try this

select TranNum from table
Group by TranNum 
Having min(Tran_seq)=max(Tran_seq) and min(Tran_seq)=0

that did it! great thanks so much

Hi I have a new wrinkle. I now need this to add account name from the AMTRAN table as well as tran_number.

So in other words there now can be the same trannum assigned to multiple account names but they could have diffrent transeq. I need to get the max tran_seq by account then by tran num

Thanks for all your help!!!

print("select t1.Tran_Num,t1.sno+t2.sno-1 as Tran_Seq from

( select TranNum , max(TRANSEQUENCE)+rownumber() over (partition by TranNum order by TranNum) as sno from AMTRAN
group by TranNum ) as t1 inner join ( select docnum , rownumber() over (partition by docnum order by docnum) as sno from CAIUPDATEAR ) as t2 on t1.TranNum =t2.docnum");

Is your query working fine?

oh yes everything you gave me before works perfectly. This is a new wrinkle where Now there can be the same trannum spread out over several diffrent accounts. So I need to find the max tran seq not only by tran num but now by account. See one account could have 3 tran seqences and an another account can have 10 but all fro the same trannum

thanks Vic

In the Partition by clause you need to include both tran num and account columns

Hi again. You have been so helpful I want to thank you again.

Everything I have tried to over come this issue has not worked so I want to try a diffrent approach. How would I write a trigger on a table that would fire on insert and create the correct tran_seq I need.

It needs to look at a table called AMTRAN grab the Account and the tran_source then the tran Number then calculate the next seq and insert that new seq with the record I am trying to insert.

Thanks Vic