Assign Group ID To Rows

Question

Post reply

Assign Group ID To Rows

cimbom_kid

SSC-Addicted

Points: 469
More actions
November 30, 2010 at 5:09 am

#241761

Hi All,
I have a problem i need to solve. I have a table:
CREATE TABLE #TMP_LOADER (
linenum int IDENTITY (1, 1)
fileline varchar(max)
group_id int)
which contains this data:
linenum, fileline
1 a
2 b
3 c
4 ------
5 d
6 e
7 ------
8 f
9 ------
10 g
'------' is the record delimiter.
How can i assign a group id so that from line numbers 1-3, group id = 1, from rows 5-6, group id = 2, row 8, group id = 3, row 10, group id = 4, etc?
Your help is greatly appreciated
Many thanks, Sal

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

DROP TABLE #TMP_LOADER

CREATE TABLE #TMP_LOADER (

linenum int IDENTITY (1, 1),

fileline varchar(max),

group_id int)

INSERT INTO #TMP_LOADER (fileline)

SELECT 'a' UNION ALL

SELECT 'b' UNION ALL

SELECT 'c' UNION ALL

SELECT '------' UNION ALL

SELECT 'd' UNION ALL

SELECT 'e' UNION ALL

SELECT '------' UNION ALL

SELECT 'f' UNION ALL

SELECT '------' UNION ALL

SELECT 'g'

SELECT linenum,

fileline,

group_id = DENSE_RANK() OVER(ORDER BY NewGroup)

FROM (

SELECT NewGroup = (100+Linenum) - gid,

linenum,

fileline

FROM (

SELECT linenum,

fileline,

gid = ROW_NUMBER() OVER(ORDER BY fileline)

FROM #TMP_LOADER

) d

WHERE NOT fileline = '------'

) c

ORDER BY linenum

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLSmasher Right there with Babe Points: 777 More actions · Answer 2

Try this one:

CREATE TABLE #TMP_LOADER_2 (

group_id int IDENTITY (1, 1)

,linenum int)

insert into #TMP_LOADER_2

select linenum from #TMP_LOADER where fileline = '-----'

UPDATE t1

SET t1.group_id = t2.group_id

FROM #TMP_LOADER t1 inner join #TMP_LOADER_2 t2 on t1.linenum = t2.linenum

thanks

SQLSmasher

cimbom_kid SSC-Addicted Points: 469 More actions · Answer 3

Hi Chris Morris,

Many thanks for your help, but the query doesn't return the results i need. The resulting query should be:

linenum, fileline, group_id

1 a, 1

2 b, 1

3 c, 1

4 ------2

5 d, 2

6 e, 2

7 ------3

8 f, 3

9 ------3

10 g, 4

Thanks again, Sal

cimbom_kid SSC-Addicted Points: 469 More actions · Answer 4

Hi SQLsmasher,

Many thanks for your help, but the query doesn't return the results i need. The resulting query should be:

linenum, fileline, group_id

1 a, 1

2 b, 1

3 c, 1

4 ------2

5 d, 2

6 e, 2

7 ------3

8 f, 3

9 ------3

10 g, 4

Thanks again, Sal

cimbom_kid SSC-Addicted Points: 469 More actions · Answer 5

Apols, linenum 9, should have group id 4 too. typo 🙁

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

--Using slow triangular join

SELECT a.linenum,

a.fileline,

COUNT(b.linenum)+1 as group_id

FROM #TMP_LOADER a

LEFT OUTER JOIN #TMP_LOADER b ON b.linenum<=a.linenum

AND b.fileline='------'

GROUP BY a.linenum,a.fileline

ORDER BY a.linenum;

"Quirky update" would perform well here

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

cimbom_kid (11/30/2010)
Hi Chris Morris,
Many thanks for your help, but the query doesn't return the results i need. The resulting query should be:
linenum, fileline, group_id
1 a, 1
2 b, 1
3 c, 1
4 ------2
5 d, 2
6 e, 2
7 ------3
8 f, 3
9 ------3
10 g, 4
Thanks again, Sal

That's not what you originally posted: -

cimbom_kid (11/30/2010)
How can i assign a group id so that from line numbers 1-3, group id = 1, from rows 5-6, group id = 2, row 8, group id = 3, row 10, group id = 4, etc?

;WITH Calculator AS (

SELECT linenum, fileline, group_id = CAST(1 AS INT)

FROM #TMP_LOADER

WHERE linenum = 1

UNION ALL

SELECT nr.linenum, nr.fileline, CASE nr.fileline WHEN '------' THEN lr.group_id + 1 ELSE lr.group_id END

FROM Calculator lr

INNER JOIN #TMP_LOADER nr ON nr.linenum = lr.linenum + 1

) SELECT *

FROM Calculator

Edit: revised method.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cimbom_kid SSC-Addicted Points: 469 More actions · Answer 8

Apologies for the misinformed post. But thanks to all for your input, Mark many thanks, your solution has worked for me.

Hopefully i can have an answer to someones problem one day.

SQLSmasher Right there with Babe Points: 777 More actions · Answer 9

cimbom_kid

in that case you can add another update statment which will change group_ID based on creteria previously inserted group header

CREATE TABLE #TMP_LOADER_2 (

group_id int IDENTITY (1, 1)

,linenum int)

insert into #TMP_LOADER_2

select linenum from #TMP_LOADER where fileline = '-----'

UPDATE t1

SET t1.group_id = t2.group_id

FROM #TMP_LOADER t1 inner join #TMP_LOADER_2 t2 on t1.linenum = t2.linenum

UPDATE #TMP_LOADER

SET group_id = (

select min(group_id) as max_group_id from #TMP_LOADER t2 where group_id is not null

and t2.linenum > #TMP_LOADER.linenum)

WHERE group_id is null

Thanks

SQLSmasher