CODEWITHSUNDEEP
sqlsql-servert-sql
mohan111
mohan111

Reputation: 8865

how to do partitioning on VARCHAR column

DECLARE @Table1 TABLE 
        (ID int, STATUS varchar(1))
    ;

    INSERT INTO @Table1
        (ID, STATUS)
    VALUES
        (1,  'A'),
        (1,  'A'),
        (1,  'A'),
        (1,  'B'),
        (1,  'A'),
        (2,  'C'),
        (2,  'C')
    ;

Script :

Select *,ROW_NUMBER()OVER(PARTITION BY STATUS ORDER BY (SELECT NULL))RN from @Table1

Getting Result Set 

ID  STATUS  RN
1   A       1
1   A       2
1   A       3
1   A       4
1   B       1
2   C       1
2   C       2

Need Output 

ID  STATUS  RN
1   A       1
1   A       2
1   A       3
1   B       1
1   A       1
2   C       1
2   C       2

Upvotes: 1

Views: 2686

Answers (2)

neer
neer

Reputation: 4092

Try this

DECLARE @Table1 TABLE 
        (ID int, STATUS varchar(1));

INSERT INTO @Table1
    (ID, STATUS)
VALUES
    (1,  'A'),
    (1,  'A'),
    (1,  'A'),
    (1,  'B'),
    (1,  'A'),
    (2,  'C'),
    (2,  'C');

;WITH Tmp
AS 
(    
    SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM @Table1
)


SELECT
    A.ID ,
    A.STATUS ,      
    ROW_NUMBER() OVER (PARTITION BY A.STATUS, (A.RowNumber - A.RN)  ORDER BY (SELECT NULL)) AS RN
FROM
(
    Select *, ROW_NUMBER() OVER(PARTITION BY STATUS ORDER BY RowNumber) AS RN from tmp

) A
ORDER BY
    A.RowNumber

Output:

ID          STATUS RN
----------- ------ ------
1           A      1
1           A      2
1           A      3
1           B      1
1           A      1
2           C      1
2           C      2

Upvotes: 1

Saqib Mustafa Abbasi
Saqib Mustafa Abbasi

Reputation: 158

Firstly, In the insert statement that you posted. How is 4 different from 1,2 and 3, if it is based on a different column then include that column as well in "row_number" in partition by sub clause. Because otherwise it will think that 'A' in 4 and 'A' in 1,2,3 are same and therefore group them together.

INSERT INTO @Table1
  (ID, STATUS)
VALUES
  (1,  'A'),   <--  1
  (1,  'A'),   <--  2
  (1,  'A'),   <--  3
  (1,  'B'),
  (1,  'A'),   <--  4
  (2,  'C'),
  (2,  'C')
;

Upvotes: 0

Related Questions