sql server programming: divide Rows into groups of n rows and find the max -- sql server programming

RE: divide Rows into groups of n rows and find the max

Alejandro Mesa
10/12/2007 11:51:01 AM

sql server programming:

Try:

DECLARE @t TABLE ([Day] INT, [Value] INT)

INSERT INTO @t VALUES(1, 10)
INSERT INTO @t VALUES(2, 60)
INSERT INTO @t VALUES(3, 25)
INSERT INTO @t VALUES(4, 46)
INSERT INTO @t VALUES(5, 67)
INSERT INTO @t VALUES(6, 81)
INSERT INTO @t VALUES(7, 12)
INSERT INTO @t VALUES(8, 45)
INSERT INTO @t VALUES(9, 24)
INSERT INTO @t VALUES(10, 87)
INSERT INTO @t VALUES(11, 100)
INSERT INTO @t VALUES(12, 29)
INSERT INTO @t VALUES(13, 55)
INSERT INTO @t VALUES(14, 45)

SELECT (([Day] - 1) / 7) + 1 AS grp, MAX([Value]) AS max_grp_Value
FROM @t
GROUP BY (([Day] - 1) / 7) + 1
go

AMB

[quoted text, click to view]

Re: divide Rows into groups of n rows and find the max

aquagal
10/12/2007 5:49:45 PM

Thanks Alenjandro.
But this doesnt work if my day values are in 100s or 1000s e.g
381,382,283,384 etc.I dont get output as 1,2,3 for Grp.
Also,it gets divided into group of 3 when it should be divided in only
2 groups(14 days/7=2 weeks)
Please see the following code that I ran

DECLARE @t TABLE ([Day] INT, [Value] INT)

INSERT INTO @t VALUES(381, 10)
INSERT INTO @t VALUES(382, 60)
INSERT INTO @t VALUES(383, 25)
INSERT INTO @t VALUES(384, 46)
INSERT INTO @t VALUES(385, 67)
INSERT INTO @t VALUES(386, 81)
INSERT INTO @t VALUES(387, 12)
INSERT INTO @t VALUES(388, 45)
INSERT INTO @t VALUES(389, 24)
INSERT INTO @t VALUES(390, 87)
INSERT INTO @t VALUES(391, 100)
INSERT INTO @t VALUES(392, 29)
INSERT INTO @t VALUES(393, 55)
INSERT INTO @t VALUES(394, 45)

SELECT (([Day] - 1) / 7) + 1 AS grp, MAX([Value]) AS max_grp_Value
FROM @t
GROUP BY (([Day] - 1) / 7) + 1
go

---------OUTPUT------------
grp max_grp_Value
55 67
56 100
57 55

Thanks.

divide Rows into groups of n rows and find the max

aquagal
10/12/2007 6:04:00 PM

Hi All,

I am using SQL server 2000.
I need to do the following.
Suppose I have a table that has daily data called DailyTable

DailyTable
--------------

Day Value
--------------
1 10
2 60
3 25
4 46
5 67
6 81
7 12
8 45
9 24
10 87
11 100
12 29
13 55
14 45

What I need to do is create groups of 7 rows(ie a week),
get the max of value from the 7 days and store in another table called
WeeklyTable
So my output needs to be calculated as follows

for days Day 1 to Day 7 in DailyTable,Max value =81.Choose day Day 6
for days Day 8 to Day 14 Max value is =100.Choose Day 11

WeeklyTable
-------------------

WeekNumber DayOfMaxValue MaxValue
------------------ ---------------------
-------------------
1 Day 6 81
2 Day 11 100

How do I write a script/Select statement to do this?
Basically it boils down to how do I divide the days into groups of 7
in SQL Server 2000?
Any help on this is highly appreciated.

Thanks in advance
Bhavana

Re: divide Rows into groups of n rows and find the max

Razvan Socol
10/13/2007 12:12:18 AM

Try something like this:

DECLARE @t TABLE ([Day] INT, [Value] INT)

INSERT INTO @t VALUES(381, 10)
INSERT INTO @t VALUES(382, 60)
INSERT INTO @t VALUES(383, 25)
INSERT INTO @t VALUES(384, 46)
INSERT INTO @t VALUES(385, 67)
INSERT INTO @t VALUES(386, 81)
INSERT INTO @t VALUES(387, 12)
INSERT INTO @t VALUES(388, 45)
INSERT INTO @t VALUES(389, 24)
INSERT INTO @t VALUES(390, 87)
INSERT INTO @t VALUES(391, 100)
INSERT INTO @t VALUES(392, 29)
INSERT INTO @t VALUES(393, 55)
INSERT INTO @t VALUES(394, 45)

SELECT (NewDay / 7) + 1 AS grp, MAX(Value) AS max_grp_Value
FROM (
SELECT [Day]-(SELECT MIN([Day]) FROM @t) as NewDay, Value
FROM @t
) x
GROUP BY (NewDay / 7) + 1

--
Razvan Socol

Re: divide Rows into groups of n rows and find the max

Alejandro Mesa
10/13/2007 1:00:01 PM

Try:

DECLARE @t TABLE ([Day] INT, [Value] INT)

INSERT INTO @t VALUES(381, 10)
INSERT INTO @t VALUES(382, 60)
INSERT INTO @t VALUES(383, 25)
INSERT INTO @t VALUES(384, 46)
INSERT INTO @t VALUES(385, 67)
INSERT INTO @t VALUES(386, 81)
INSERT INTO @t VALUES(387, 12)
INSERT INTO @t VALUES(388, 45)
INSERT INTO @t VALUES(389, 24)
INSERT INTO @t VALUES(390, 87)
INSERT INTO @t VALUES(391, 100)
INSERT INTO @t VALUES(392, 29)
INSERT INTO @t VALUES(393, 55)
INSERT INTO @t VALUES(394, 45)

;WITH cte
AS
(
SELECT
[Day],
[Value],
(((row_number() OVER(ORDER BY [Day] ASC)) - 1) / 7) + 1 AS grp
FROM @t
)
SELECT grp, MAX([Value]) AS max_grp_Value
FROM cte
GROUP BY grp
go

AMB

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.