SQL Server: DENSE_RANK ()

Question

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 !!

Don't hard code what? Do you think someone is going to give you 8 update statements? — Martin Smith, Sep 19 '11 at 20:34
No, I mean don't use like case batch when 2 then 1. etc. you wont believe how funny responses I get in other forums *.* — Bhupinder Singh, Sep 19 '11 at 20:37
So, if you have a `where id in (8,3)` it should return a **different** result? — ypercubeᵀᴹ, Sep 19 '11 at 21:15
@ ypercube: I mean if my select statement is changing, I dont want to change my code. Note: I can create temporary tables and add required columns to get the proper answer. — Bhupinder Singh, Sep 19 '11 at 23:00

score 4 · Answer 1 · answered Sep 19 '11 at 21:03

/*Please provide DDL like the below for future questions*/
DECLARE @tableA TABLE
(
ID INT PRIMARY KEY,
Batch INT,
New_Batch INT NULL
)

INSERT INTO @tableA(ID,Batch)
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL   
SELECT 3,2 UNION ALL   
SELECT 4,2 UNION ALL   
SELECT 5,2 UNION ALL   
SELECT 6,3 UNION ALL   
SELECT 7,4 UNION ALL   
SELECT 8,5   

/*Answer*/
;WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (ORDER BY 
                      CASE
                        WHEN Batch IN (SELECT Batch
                                       FROM   @tableA
                                       WHERE  ID IN ( 3, 8 )) 
                         THEN 0 
                         ELSE 1 END, Batch) AS NB
         FROM   @tableA)
UPDATE T
SET    New_Batch = NB

SELECT *
FROM   @tableA

If select statement is changing, I dont want to change my code ! This is what I meant be HARD-CODE ! Thanks for query — Bhupinder Singh, Sep 19 '11 at 22:58
Well you'll need to use one of the well established methods for passing in a variable list of parameters then. e.g. see http://www.sommarskog.se/arrays-in-sql.html — Martin Smith, Sep 19 '11 at 23:01

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

declare @T table
(
  ID int,
  Batch char(2),
  New_Batch char(2)
)

insert into @T values
(1,   '01',    NULL),
(2,   '01',    NULL),
(3,   '02',    NULL),
(4,   '02',    NULL),
(5,   '02',    NULL),
(6,   '03',    NULL),
(7,   '04',    NULL),
(8,   '05',    NULL)

;with C as
(
  select T1.New_Batch,
         dense_rank() over(order by -T2.ID desc, T1.Batch) as rn
  from @T as T1
    left outer join (select Batch, ID 
                     from @T
                     where ID in (3, 8)) as T2
      on T1.Batch = T2.Batch 
)
update C 
set New_Batch = right(100+rn, 2)


select *
from @T
order by ID

Try here : https://data.stackexchange.com/stackoverflow/q/113031/

SQL Server: DENSE_RANK ()

2 Answers2