Aggregation conundrum
-
I have a table that contains information as follows:
Col1 Col2 Col3 Col4
1 12 50 1
1 12 50 2
1 12 100 3
1 12 100 4
1 12 100 5
1 12 50 6
1 12 50 7
1 13 50 1
1 13 50 2
I want to return the following
col1 col2 col3 col4
1 12 50 1-2
1 12 100 3-5
1 12 50 6-7
1 13 50 1-2
Basically grouped by col1, col2 and col3 I want the range of col4 (max and min) BUT I want the grouping to be sensitive to the order or col4. So if in order col4, col3 amount changes, then is repeated again later, I want to have the values group accordingly. So in my example here col3 goes from 50 for 2 records to 100 for three then back down to 50 for two. I don't want to have 50 show up with a max of 7 and a min of 1. I want it to show 50 1-2,
100 3-5 and 50 6-7
Select col1, col2, col3, max(col4), min(col4)
from table
group by col1, col2, col3
Doesn't work!
Thanks in advance.
-
Better do this at the client side:
SET NOCOUNT ON
Declare @tbl Table (a int, b int, c int ,d int)
insert into @tbl SELECT 1, 12 ,50 ,1
insert into @tbl SELECT 1 ,12 ,50 ,2
insert into @tbl SELECT 1 ,12 ,100 ,3
insert into @tbl SELECT 1 ,12 ,100 ,4
insert into @tbl SELECT 1 ,12 ,100 ,5
insert into @tbl SELECT 1 ,12 ,50 ,6
insert into @tbl SELECT 1 ,12 ,50 ,7
insert into @tbl SELECT 1 ,13 ,50 ,1
insert into @tbl SELECT 1 ,13 ,50 ,2
/*
1 12 50 1-2
1 12 100 3-5
1 12 50 6-7
1 13 50 1-2
*/
SELECT a,b,c, MIN , MAX
FROM
(SELECT a.a,a.b,a.c,a.d,
ISNULL((SELECT MAX(d)+1 FROM @tbl WHERE a.a=a and a.b=b AND d<a.d AND c<>a.c),
(SELECT MIN(d) FROM @tbl WHERE a.b=b and a.c=c)) [MIN],
ISNULL((SELECT MIN(d)-1 FROM @tbl WHERE a.a=a and a.b=b AND d>a.d AND c<>a.c) ,
(SELECT MAX(d) FROM @tbl WHERE a.b=b and a.c=c)) [MAX]
FROM @tbl a) derivtbl
WHERE d=MIN
Kindest Regards,Vasc
-
Beautiful! I was messing with this and going a wholely different route.
I wasn't born stupid - I had to study.
-
Improved plan
SELECT X.a,X.b,X.c,MAX(Y.D),X.d
FROM
(SELECT a.a,a.b,a.c,a.d
FROM @tbl a LEFT OUTER JOIN @tbl b
ON a.a=b.a AND a.b=b.b AND a.d=b.d-1
WHERE a.c!=b.c or b.a IS NULL
) X
INNER JOIN
(SELECT a.a,a.b,a.c,a.d
FROM @tbl a LEFT OUTER JOIN @tbl b
ON a.a=b.a AND a.b=b.b AND a.d=b.d+1
WHERE a.c!=b.c OR b.a IS NULL
) Y
ON X.a=Y.a AND X.b=Y.b AND X.C=Y.C
WHERE Y.D<=X.d
GROUP BY X.a,X.b,X.c,X.d
Kindest Regards,Vasc
-
Care to outline the logic of this thing???????????????????????
-
Thanks to Vasc!
The performance of the second one is much faster than the first one.
It is under a minute in a huge table. Many thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply