quote:

---

Originally posted by ben_53

I have a tableA: (ID int, batch int, new_batch int)

ID and batch are populated already:

ID Batch New_Batch
1 01 NULL
2 01 NULL
3 02 NULL
4 02 NULL
5 02 NULL
6 03 NULL
7 04 NULL
8 05 NULL
Now I want to populate New_batch according to the following select statement.

(select batch from tableA where id in (3,8))
now for this select statement, we get batch = 02 and batch =5. now I want to assign new_batch such that the result of select statement should be ordered first (batch02 then batch05) and the remaining data should be ordered in increasing order of batch NOT in select statement. (batch 1,3,4) result should be:
ID Batch New_Batch
1 01 03
2 01 03
3 02 01
4 02 01
5 02 01
6 03 04
7 04 05
8 05 02
Thanks. PS: DENSE_RANK() can be used, and please dont hard-code !!

---

SELECT 
	a.[id], 
	a.Batch, 
	[New_Batch] = dense_rank() OVER (ORDER BY CASE WHEN a.Batch IN 
								(SELECT b.Batch FROM #tableA b WHERE b.[id] IN (3, 8))
							THEN a.batch
							ELSE a.Batch + (SELECT max(b.Batch) FROM #tableA b WHERE b.[id] IN (3, 8))
						END)
FROM #tableA a