Group by every nth record - 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 2005 Forums

Transact-SQL (2005)

Group by every nth record

Author

Topic

sunny_mash
Starting Member

2 Posts

Posted - 2010-03-12 : 14:25:52

Is it possible that i can group data for every nth record.

I have a table with the following structure

DateTime | Value
2010-01-01 00:08:25.000 | 5
2010-01-01 00:08:55.000 | 6
2010-01-01 00:06:55.000 | 8
2010-01-01 00:07:25.000 | 3
2010-01-01 00:07:55.000 | 7
2010-01-01 00:09:25.000 | 2
2010-01-01 00:13:25.000 | 3
2010-01-01 00:13:55.000 | 4
2010-01-01 00:14:25.000 | 2
2010-01-01 00:14:55.000 | 3

I want to now group the data based on 2 records each and have the min and max value. The output need to appear like this (for the DateTime column, i only need to consider the second value)

Output Required

DateTime | Min |Max
2010-01-01 00:08:55.000 | 5 | 6
2010-01-01 00:07:25.000 | 3 | 8
2010-01-01 00:09:25.000 | 2 | 7
2010-01-01 00:13:55.000 | 3 | 4
2010-01-01 00:14:55.000 | 2 | 3

Can this be achieved via a single SQL Statement?

Thanks in advance for the help?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-12 : 16:45:05

[CODE]declare @tbl1 table (
row int,
dt datetime,
value int
)

INSERT INTO @tbl1
SELECT 1, '2010-01-01 00:08:25.000', 5 union all
SELECT 2, '2010-01-01 00:08:55.000', 6 union all
SELECT 3, '2010-01-01 00:06:55.000', 8 union all
SELECT 4, '2010-01-01 00:07:25.000', 3 union all
SELECT 5, '2010-01-01 00:07:55.000', 7 union all
SELECT 6, '2010-01-01 00:09:25.000', 2 union all
SELECT 7, '2010-01-01 00:13:25.000', 3 union all
SELECT 8, '2010-01-01 00:13:55.000', 4 union all
SELECT 9, '2010-01-01 00:14:25.000', 2 union all
SELECT 10, '2010-01-01 00:14:55.000', 3

select distinct max(dt) over(partition by (row - 1) / 2) dt, min(value) over(partition by (row - 1) / 2) minVal, max(value) over(partition by (row - 1) / 2) maxVal
from @tbl1
order by dt[/CODE]
Change the "2" to be your grouping size.
=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

Sachin.Nand

2937 Posts

Posted - 2010-03-13 : 01:11:04

[code]
declare @tbl1 table (
row int,
dt datetime,
value int
)

INSERT INTO @tbl1
SELECT 1, '2010-01-01 00:08:25.000', 5 union all
SELECT 2, '2010-01-01 00:08:55.000', 6 union all
SELECT 3, '2010-01-01 00:06:55.000', 8 union all
SELECT 4, '2010-01-01 00:07:25.000', 3 union all
SELECT 5, '2010-01-01 00:07:55.000', 7 union all
SELECT 6, '2010-01-01 00:09:25.000', 2 union all
SELECT 7, '2010-01-01 00:13:25.000', 3 union all
SELECT 8, '2010-01-01 00:13:55.000', 4 union all
SELECT 9, '2010-01-01 00:14:25.000', 2 union all
SELECT 10, '2010-01-01 00:14:55.000', 3

select dt,minvalue,maxvalue from
(
select *,minvalue=(
select min(value)from @tbl1 t1 where t2.row between t1.row and t1.row+1
having COUNT(*)=2
)
,maxvalue=(
select max(value)from @tbl1 t1 where t2.row between t1.row and t1.row+1
having COUNT(*)=2
)
from @tbl1 t2

)t where (row%2)=0
[/code]

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 01:39:08

[code]declare @N int,@Grp int
SELECT @N=2,-- number of records by which you need to group
@Grp=(COUNT(1) OVER ())/@N
FROM @tbl1

SELECT GrpNo,MAX(dt),MIN(value),MAX(value)
FROM
(
SELECT NTILE(@Grp) OVER (ORDER BY dt) AS GrpNo,dt,value FROM @tbl1
)t
GROUP BY GrpNo
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunny_mash
Starting Member

2 Posts

Posted - 2010-03-13 : 05:50:33

Thanks guys, all the solutions work. You have made my day

Sachin.Nand

2937 Posts

Posted - 2010-03-13 : 05:57:36

You are welcome

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 10:56:46

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources