CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vishal prabhu lawande
Vishal prabhu lawande

Reputation: 231

Group By column within the range

I have the following data (Only A and B column. I do not have column C in my schema).

A   | B   |  C  | 
________________
1     2     G1
2     70    G1
3     91    G1
4     25    G2
5     30    G2 
6     95    G2
7     91    G3
8     75    G4
9     92    G4
10    93    G5

Please Note: I want to Group my data within the range of 0 to 90. i.e. the data within 0 to 90 is to be grouped in one group. The next group will be up to the next 90. So each group with in the range of 0 to 90.

My expectation is mentioned in Column C, I am using SQL Server 2008.

Upvotes: 1

Views: 94

Answers (2)

Arulkumar
Arulkumar

Reputation: 13247

First using ROW_NUMBER() I assign the Group id value for the >90 values, then fill the NULL values by next Group id value.

Sample execution with the given sample data:

DECLARE @TestTable TABLE  (A INT, B INT, C VARCHAR (3));

INSERT INTO @TestTable (A, B) 

SELECT 1 , 2  UNION
SELECT 2 , 70 UNION
SELECT 3 , 91 UNION
SELECT 4 , 25 UNION
SELECT 5 , 30 UNION
SELECT 6 , 95 UNION
SELECT 7 , 91 UNION
SELECT 8 , 75 UNION
SELECT 9 , 92 UNION
SELECT 10, 93;

UPDATE T
SET T.C = R.GRow
FROM @TestTable T
JOIN (  SELECT A, B, 'G' + CAST(ROW_NUMBER() OVER (ORDER BY A) AS VARCHAR (3)) AS GRow  
        FROM @TestTable
        WHERE B > 90 ) R ON R.B = T.B AND R.A = T.A;

UPDATE TT
SET TT.C = RR.GVal
FROM @TestTable TT 
JOIN (  SELECT A, B, ISNULL(C, (SELECT TOP 1 C FROM @TestTable WHERE A > TE.A AND C IS NOT NULL ORDER BY A ASC)) AS GVal
        FROM @TestTable TE ) RR ON RR.B = TT.B AND RR.A = TT.A;

SELECT * FROM @TestTable

Upvotes: 3

TriV
TriV

Reputation: 5148

You could ROW_NUMBER and SUM OVER() to calculate GroupId like this

DECLARE @SampleData AS TABLE
(
    A int,
    B int
)

INSERT INTO @SampleData
VALUES 
(1 , 2 ),
(2 , 70),
(3 , 91),
(4 , 25),
(5 , 30), 
(6 , 95),
(7 , 91),
(8 , 75),
(9 , 92),
(10, 93)

;WITH temp AS
(
    SELECT *, Row_number() OVER(ORDER BY A ) AS Rn
    FROM @SampleData sd
)
,temp1 AS
(
    SELECT t.A, t.B, CASE   WHEN t2.B IS NULL OR t2.B > 90 THEN 1
                       ELSE 0
                 END as PreviousCount
    FROM temp t
    LEFT JOIN temp t2 ON t.Rn = t2.Rn + 1  -- previous row
)
SELECT t.A, t.B, 'G' + CAST(sum(t.PreviousCount) over(ORDER BY t.A) AS varchar(5)) AS GroupId
FROM temp1 t

Demo link: http://rextester.com/TXP71819

Note: For sql-server 2012+, you could use LAG directly to get previous row.

Upvotes: 0

Related Questions