Grouping and numbering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping and numbering

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2011-05-19 : 14:13:29

I have a table which has data similar to the following:


flag	posn	dif
----    ----    --------
1	1	0.000162
1	2	0.000178
1	3	0.000075
1	4	0.000037
0	5	0.000059
1	6	0.000017
1	7	0.000001
0	8	0.000129
0	9	0.000054
1	10	0.000007
0	11	0.000082
0	12	0.000013
0	13	0.000048
1	14	0.000004
1	15	0.000037
1	16	0.000013
1	17	0.000014
1	18	0.000009
1	19	0.000011
0	20	0.000048
1	21	0.000014
1	22	0.000075
0	23	0.000068
1	24	0.000014
1	25	0.000027
1	26	0.000034
1	27	0.000028

What I want is an output like foloowing:


Col	flag	posn	dif
----    ----    ----    --------
1	1	1	0.000162
1	1	2	0.000178
1	1	3	0.000075
1	1	4	0.000037
	0	5	0.000059
2	1	6	0.000017
2	1	7	0.000001
	0	8	0.000129
	0	9	0.000054
3	1	10	0.000007
	0	11	0.000082
	0	12	0.000013
	0	13	0.000048
4	1	14	0.000004
4	1	15	0.000037
4	1	16	0.000013
4	1	17	0.000014
4	1	18	0.000009
4	1	19	0.000011
	0	20	0.000048
5	1	21	0.000014
5	1	22	0.000075
	0	23	0.000068
6	1	24	0.000014
6	1	25	0.000027
6	1	26	0.000034
6	1	27	0.000028

ie. To have another Column, which groups consecutive 1 as the value for flag and put a # starting from 0, incrementing by 1.
No number for flag = 0.

Thanks in advance

Srinika

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 14:48:54

This works, but it's kludgey:

WITH a(col, flag, posn, dif) AS (
SELECT CASE WHEN flag=1 THEN 1+posn-RANK() OVER (PARTITION BY flag ORDER BY posn) ELSE NULL END, * FROM myTable)
SELECT CASE WHEN flag=1 THEN DENSE_RANK() OVER (ORDER BY col)-1 END col, flag, posn, dif FROM a ORDER BY posn

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2011-06-02 : 11:09:30

Thanks robvolk, works well for me

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources