September 5, 2015 at 2:29 pm
I need some help with the following situation
I have a table with the follow row and sample data
RowID RecordType Data
1 0001 ABCD
2 0002 ACDE
3 0003 EFGH
4 0003 EDF
5 0005 CDED
6 0001 IEF
7 0002 KEF
8 0003 IGF
9 0004 DGC
10 0001 IIKK
11 0002 KKKK
12 0005 OOOO
So, I want to add a column called ID, the first ID would start as 1 for the first record type 0001 and it is going repeat as 1 until next row when 0001 appears and when the next 0001 record type appear it would increment 1. so the output of the record set would appear as
RowID RecordType Data ID
1 0001 ABCD 1
2 0002 ACDE 1
3 0003 EFGH 1
4 0003 EDF 1
5 0005 CDED 1
6 0001 IEF 2
7 0002 KEF 2
8 0003 IGF 2
9 0004 DGC 2
10 0001 IIKK 3
11 0002 KKKK 3
12 0005 OOOO 3
Thank you very much in advance for all your help.
September 5, 2015 at 4:46 pm
QQ-485619 (9/5/2015)
I need some help with the following situationI have a table with the follow row and sample data
RowID RecordType Data
1 0001 ABCD
2 0002 ACDE
3 0003 EFGH
4 0003 EDF
5 0005 CDED
6 0001 IEF
7 0002 KEF
8 0003 IGF
9 0004 DGC
10 0001 IIKK
11 0002 KKKK
12 0005 OOOO
So, I want to add a column called ID, the first ID would start as 1 for the first record type 0001 and it is going repeat as 1 until next row when 0001 appears and when the next 0001 record type appear it would increment 1. so the output of the record set would appear as
RowID RecordType Data ID
1 0001 ABCD 1
2 0002 ACDE 1
3 0003 EFGH 1
4 0003 EDF 1
5 0005 CDED 1
6 0001 IEF 2
7 0002 KEF 2
8 0003 IGF 2
9 0004 DGC 2
10 0001 IIKK 3
11 0002 KKKK 3
12 0005 OOOO 3
Thank you very much in advance for all your help.
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
DECLARE @SAMPLE_DATA TABLE
(
RowID INT NOT NULL
,RecordType CHAR(4) NOT NULL
,Data VARCHAR(10) NOT NULL
);
INSERT INTO @SAMPLE_DATA(RowID,RecordType, Data)
VALUES ( 1 ,'0001' ,'ABCD')
,( 2 ,'0002' ,'ACDE')
,( 3 ,'0003' ,'EFGH')
,( 4 ,'0003' , 'EDF')
,( 5 ,'0005' ,'CDED')
,( 6 ,'0001' , 'IEF')
,( 7 ,'0002' , 'KEF')
,( 8 ,'0003' , 'IGF')
,( 9 ,'0004' , 'DGC')
,( 10 ,'0001' ,'IIKK')
,( 11 ,'0002' ,'KKKK')
,( 12 ,'0005' ,'OOOO')
;
;WITH BASE_RANK AS
(
SELECT
SD.RowID
,DENSE_RANK() OVER
(
ORDER BY SD.RowID
) DRNK
FROM @SAMPLE_DATA SD
WHERE SD.RecordType = '0001'
)
,UPPER_BOUNDRY AS
(
SELECT MAX(RowID) AS LAST_ROWID
FROM @SAMPLE_DATA
)
,BOUNDRIES AS
(
SELECT
BR.RowID AS RFROM
,ISNULL(BNEXT.RowID,UB.LAST_ROWID + 1) AS RTO
,BR.DRNK AS ID
FROM BASE_RANK BR
LEFT OUTER JOIN BASE_RANK BNEXT
ON BR.DRNK = BNEXT.DRNK - 1
CROSS APPLY UPPER_BOUNDRY UB
)
SELECT
SD.RowID
,SD.RecordType
,SD.Data
,BR.ID
FROM @SAMPLE_DATA SD
OUTER APPLY BOUNDRIES BR
WHERE SD.RowID >= BR.RFROM
AND SD.RowID < BR.RTO
;
Results
RowID RecordType Data ID
----------- ---------- ---------- -----
1 0001 ABCD 1
2 0002 ACDE 1
3 0003 EFGH 1
4 0003 EDF 1
5 0005 CDED 1
6 0001 IEF 2
7 0002 KEF 2
8 0003 IGF 2
9 0004 DGC 2
10 0001 IIKK 3
11 0002 KKKK 3
12 0005 OOOO 3
September 6, 2015 at 8:00 am
1. How many rows are in the actual table?
2. Which column is the clustered index on?
3. How often does this need to be done?
4. Is performance important?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2015 at 10:06 am
Jeff Moden (9/6/2015)
@QQ-485619,1. How many rows are in the actual table?
2. Which column is the clustered index on?
3. How often does this need to be done?
4. Is performance important?
Jeff,
I took Erikur's solution into a CTE with recursion to get the right result. Here's the query:
DBCC FREEPROCCACHE;
CREATE TABLE #SAMPLE_DATA (
RowID int NOT NULL PRIMARY KEY CLUSTERED,
RecordType CHAR(4) NOT NULL,
Data VARCHAR(10) NOT NULL
);
INSERT INTO #SAMPLE_DATA(RowID,RecordType, Data)
VALUES ( 1 ,'0001' ,'ABCD')
,( 2 ,'0002' ,'ACDE')
,( 3 ,'0003' ,'EFGH')
,( 4 ,'0003' , 'EDF')
,( 5 ,'0005' ,'CDED')
,( 6 ,'0001' , 'IEF')
,( 7 ,'0002' , 'KEF')
,( 8 ,'0003' , 'IGF')
,( 9 ,'0004' , 'DGC')
,( 10 ,'0001' ,'IIKK')
,( 11 ,'0002' ,'KKKK')
,( 12 ,'0005' ,'OOOO');
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
WITH CTE AS (
SELECT SD.RowID, SD.RecordType, SD.Data, 1 AS GROUPING_VALUE
FROM #SAMPLE_DATA AS SD
WHERE SD.RowID = 1
UNION ALL
SELECT SD.RowID, SD.RecordType, SD.Data,
C.GROUPING_VALUE + CASE WHEN SD.RecordType < C.RecordType THEN 1 ELSE 0 END AS GROUPING_VALUE
FROM #SAMPLE_DATA AS SD
INNER JOIN CTE AS C
ON SD.RowID = C.RowID + 1
)
SELECT *
FROM CTE
ORDER BY RowID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
DROP TABLE #SAMPLE_DATA;
Here's the results:
RowIDRecordTypeDataGROUPING_VALUE
10001ABCD1
20002ACDE1
30003EFGH1
40003EDF1
50005CDED1
60001IEF2
70002KEF2
80003IGF2
90004DGC2
100001IIKK3
110002KKKK3
120005OOOO3
Here's the stats:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#SAMPLE_DATA________________________________________________________________________________________________________000000000004'. Scan count 0, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 73, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 64 ms.
The .SQLPLAN file is attached. I have two questions for you. 1.) Might an approach to use LAG with a recursive CTE in SQL 2012 be a viable approach, or is this as good as Erikur's query? 2.) What's the best way to reproduce LAG() in SQL 2008?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply