Wednesday, December 24, 2003

Running number !!

This is one of the question a senior DBA asked me in an interview. Let me explain the question which is really interesting (at least for me :) )

Sample table: Table1
ID, Name
1, aaa
2, aaa
3, aaa

Sample table: Table2
ID, Name
Null, bbb
Null, bbb
Null, bbb

Required result
ID, Name
1, aaa
2, aaa
3, aaa
4, bbb
5, bbb
6, bbb

Hmm actually as I was then a Project leader I have lost touch with code :) so it took some time before I answered him properly. At last the query which I wrote is as follows:

Select a.[ID], a.[Name] from Table1 a
  Union
   select distinct a.[ID] + (Select max(ID) from Table1), b.[Name] from Table1 a, Table2 b
order by a.[ID]

No comments: