Grouping with windowing functions

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

General SQL Server Forums

New to SQL Server Programming

Grouping with windowing functions

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-29 : 03:23:56

I'm on the slow side today and for some funky reason I can't seem to get my head around this: I have an SSIS package error logging table that I need to query and I can't group it the way I want to save my life it seems. Heres the deal:

DECLARE @errors table (
  ErrorID int IDENTITY (1, 1),
  ErrorGUID uniqueidentifier
  )
INSERT INTO @errors
VALUES 
	('D07DCC50-EABF-4A56-A402-B4ABEE9B6B13'),
	('D07DCC50-EABF-4A56-A402-B4ABEE9B6B13'),
	('4E50B86B-5FA7-4E2B-B781-EE250B42C4EA'),
	('4E50B86B-5FA7-4E2B-B781-EE250B42C4EA'),
	('1A4F31BD-E389-44A3-9A41-08555B691BEA'),
	('1A4F31BD-E389-44A3-9A41-08555B691BEA')
SELECT * FROM @errors

--> Expected output
ErrorID	Rowgroup	ErrorGUID
1	1		D07DCC50-EABF-4A56-A402-B4ABEE9B6B13
2	1		D07DCC50-EABF-4A56-A402-B4ABEE9B6B13
3	2		4E50B86B-5FA7-4E2B-B781-EE250B42C4EA
4	2		4E50B86B-5FA7-4E2B-B781-EE250B42C4EA
5	3		1A4F31BD-E389-44A3-9A41-08555B691BEA
6	3		1A4F31BD-E389-44A3-9A41-08555B691BEA

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-29 : 03:50:38

Rowgroup = dense_rank() over (order by ErrorGUID)

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-29 : 04:17:33

Hmm...but this way it will be ordered by the GUID (2 - 3 - 1). Any way to give them the same order as the ErrrID...?

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-29 : 04:47:25

[code]
; with err as
(
SELECT *,
grp_no = min(ErrorID) over (partition by ErrorGUID)
FROM @errors
)
select *, Rowgroup = dense_rank() over (order by grp_no)
from err
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-29 : 05:03:16

Thanx alot khtan, right on the money! I don't think I could have pulled this off today...maybe some other day though...I hope... :)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-29 : 05:10:39

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources