0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for biology researchers, academics, and students

declare @tmpTable table
	(ID Varchar(1), 
	 First varchar(4), 
	 Last varchar(5), 
	 Phone varchar(13),
	 NonKeyField varchar(4))

insert into @tmpTable select 'A', 'John', 'Smith', '(555)555-1234', 'ASDF'
insert into @tmpTable select 'B', 'John', 'Smith', '(555)555-1234', 'GHJK'
insert into @tmpTable select 'C', 'Jane', 'Smith', '(555)555-1234', 'QWER'
insert into @tmpTable select 'D', 'John', 'Smith', '(555)555-1234', 'RTYU'
insert into @tmpTable select 'E', 'Bill', 'Blake', '(555)555-0000', 'BVNM'
insert into @tmpTable select 'F', 'Bill', 'Blake', '(555)555-0000', '%^&*'
insert into @tmpTable select 'G', 'John', 'Smith', '(555)555-1234', '!#RF'

select  
  dense_rank() over (partition by First, Last, Phone order by ID) NewIDNum
  , *  
from @tmpTable 
order by ID

fork query

edited oct 2 12 gpetrioli

created oct 2 12 Scott Chamberlain