Sequential groups - 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 2012 Forums

Transact-SQL (2012)

Sequential groups

Author

Topic

derach2000
Starting Member

37 Posts

Posted - 2015-02-18 : 08:47:18

Hi,

I'm having some problems with row_number function

i have this set of rows
2015-01-01 red
2015-01-02 red
2015-01-03 red
2015-01-04 green
2015-01-05 green
2015-01-06 red
2015-01-07 red

i want to get the first and the last row of each group.

is a cursor the only solution?

any ideas?

Kind regads

Armin Mahmutovic
student of Clarion,C#,Crystal and SQL martial arts

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 09:55:17

how are you grouping (by the date or the color)? and what are the column names?

derach2000
Starting Member

37 Posts

Posted - 2015-02-23 : 04:17:53

Hi,

Grouping is by color.

I went with a while loop. Save row data in @variables and
when I detect a different data, just write the data to temp table.

Armin Mahmutovic
student of Clarion,C#,Crystal and SQL martial arts

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-23 : 07:52:56

you can easily do this using the first value and last value windowing functions in sql server 2012

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-23 : 08:16:55

[code]
-- *** Test Data ***
CREATE TABLE #t
(
YourDate date NOT NULL
,YourCol varchar(20) NOT NULL
);
INSERT INTO #t
VALUES ('20150101', 'red')
,('20150102', 'red')
,('20150103', 'red')
,('20150104', 'green')
,('20150105', 'green')
,('20150106', 'red')
,('20150107', 'red');
-- *** End Test Data ***

WITH Grps
AS
(
SELECT YourDate, YourCol
,ROW_NUMBER() OVER (ORDER BY YourDate)
- ROW_NUMBER() OVER (PARTITION BY YourCol ORDER BY YourDate) AS Grp
FROM #t
)
,Packed
AS
(
SELECT YourCol, Grp
,MIN(YourDate) AS FirstDate
,MAX(YourDate) AS LastDate
FROM Grps
GROUP BY YourCol, Grp
)
SELECT YourCol, FirstDate, LastDate
FROM Packed
ORDER BY FirstDate;
[/code]

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-02-24 : 03:33:16

First_Value(Date) Over(Partition by Color order by date asc ), Last_Value(Date) Over(Partition by Color order by date desc)

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources