Group By - 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 2008 Forums

Transact-SQL (2008)

Group By

Author

Topic

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 07:55:49

Hi guys
actually almost 2 days im trying to find solution myself but i already gave up. dont know what to do :(

this is data
R A B C D E
1 3 20120817 07:29:25PM mrt 40
2 3 20120817 07:29:26PM mrt 40
3 3 20120817 07:29:33PM mrt 90
4 3 20120817 07:30:11PM 987 40
5 3 20120817 07:30:12PM 987 40
6 3 20120817 07:30:43PM 987 80
7 3 20120817 07:30:45PM 987 90
8 3 20120817 07:31:02PM mrt 40
9 3 20120817 07:31:10PM mrt 90

R = Row number

i have to group this one with column d and get the minimum and maximum time (column c) but not ordinary group.
i mean
* row 1-2-3 is one group and i have to group by in this 3 lines.
* row 4-5-6-7 is another group and i have to group in this 4 lines
* row 8-9 is another group also.

i try to create column f and put group number using function ( was suppose to be variable, if column d is differen than variable was suppose put group number, but didnt success :( )

any idea, how can i do it.

thanks a lot

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 08:12:23

[code]SELECT
*,
MIN(c) OVER(PARTITION BY GroupId) AS MinValue,
MAX(c) OVER(PARTITION BY GroupId) AS MaxValue
FROM
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY R)-
ROW_NUMBER() OVER (PARTITION BY D ORDER BY R) AS GroupId
FROM
YourTable
)s[/code]

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 08:26:02

thanks for answer but i guess i explained wrong, im sorry for my silly/crappy english.

my expectation output should be like this actually.

DATA:

R A B C D E
1 3 20120817 07:29:25PM mrt 40
2 3 20120817 07:29:26PM mrt 40
3 3 20120817 07:29:33PM mrt 90
4 3 20120817 07:30:11PM 987 40
5 3 20120817 07:30:12PM 987 40
6 3 20120817 07:30:43PM 987 80
7 3 20120817 07:30:45PM 987 90
8 3 20120817 07:31:02PM mrt 40
9 3 20120817 07:31:10PM mrt 90

OUTPUT:
3 20120817 mrt 07:29:25PM 07:29:33PM
3 20120817 987 07:30:11PM 07:30:45PM
3 20120817 mrt 07:31:02PM 07:31:10PM

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-20 : 08:47:07

That's mean you need to group by similar value of adjacent rows in a column D, right ?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Welcome for all question. Let us know if our solution solved your problem.

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 08:47:30

in sunitabeck is giving wrong result, cause of row counts might chance it. i mean mrt value for different times in each group sometimes can be 5 lines or it might be 2 lines.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-20 : 08:48:24

[code]DECLARE @Sample TABLE
(
R INT NOT NULL,
A INT NOT NULL,
B DATE NOT NULL,
C TIME(0) NOT NULL,
D VARCHAR(10) NOT NULL,
E INT NOT NULL
);

INSERT @Sample
(
R,
A,
B,
C,
D,
E
)
VALUES (1, 3, '20120817', '19:29:25', 'mrt', 40),
(2, 3, '20120817', '19:29:26', 'mrt', 40),
(3, 3, '20120817', '19:29:33', 'mrt', 90),
(4, 3, '20120817', '19:30:11', '987', 40),
(5, 3, '20120817', '19:30:12', '987', 40),
(6, 3, '20120817', '19:30:43', '987', 80),
(7, 3, '20120817', '19:30:45', '987', 90),
(8, 3, '20120817', '19:31:02', 'mrt', 40),
(9, 3, '20120817', '19:31:10', 'mrt', 90);

-- SwePeso
;WITH cteSource(A, B, C, D, theGrp)
AS (
SELECT A,
B,
C,
D,
ROW_NUMBER() OVER (ORDER BY R) - ROW_NUMBER() OVER (PARTITION BY A, B, D ORDER BY R) AS theGrp
FROM @Sample
)
SELECT A,
B,
D,
MIN(C) AS theMin,
MAX(C) AS theMax
FROM cteSource
GROUP BY A,
B,
D,
theGrp
ORDER BY A,
B,
MIN(C)[/code]

N 56°04'39.26"
E 12°55'05.63"

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 08:50:58

my target is the get minimum and maximum time for each group (column d)
not all the lines. if u will check the output.

output 1 line grouped by row1-row2-row3
output 2 line grouped by row4-row5-row6-row7
output 3 line grouped by row8-row9

this data rows ordered by. if i will check it by human, i can see it, but i cant programme it in sql.

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 09:17:45

thank you so much guys for help.
i really dont know what to say or how to thank more in english :),

u are really saved my week.

THANK YOU SOO MUCH

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 11:56:13

i know this topic going longer, when i do longer test i realized some errors.

if value might like this
(1, 3, '20120817', '19:29:25', 'mrt', 40),
(2, 3, '20120817', '19:29:26', 'mrt', 40),
(3, 3, '20120817', '19:29:33', 'mrt', 90),
(4, 3, '20120817', '19:30:11', 'mrt', 40),
(5, 3, '20120817', '19:30:12', 'mrt', 40),
(6, 3, '20120817', '19:30:43', 'mrt', 80),
(7, 3, '20120817', '19:30:45', 'mrt', 90),
(8, 3, '20120817', '19:31:02', 'mrt', 40),
(9, 3, '20120817', '19:31:10', 'mrt', 90);

it returns one line instead of 3 lines. 'cause of PARTITION BY A, B, D. i couldnt realize how to fix it :(
i mean we should column e but how will i integrate it?

output 1 line grouped by row1-row2-row3
output 2 line grouped by row4-row5-row6-row7
output 3 line grouped by row8-row9

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 12:04:46

quote:
Originally posted by alberto_pompeo

i know this topic going longer, when i do longer test i realized some errors.

if value might like this
(1, 3, '20120817', '19:29:25', 'mrt', 40),
(2, 3, '20120817', '19:29:26', 'mrt', 40),
(3, 3, '20120817', '19:29:33', 'mrt', 90),
(4, 3, '20120817', '19:30:11', 'mrt', 40),
(5, 3, '20120817', '19:30:12', 'mrt', 40),
(6, 3, '20120817', '19:30:43', 'mrt', 80),
(7, 3, '20120817', '19:30:45', 'mrt', 90),
(8, 3, '20120817', '19:31:02', 'mrt', 40),
(9, 3, '20120817', '19:31:10', 'mrt', 90);

it returns one line instead of 3 lines. 'cause of PARTITION BY A, B, D. i couldnt realize how to fix it :(
i mean we should column e but how will i integrate it?

output 1 line grouped by row1-row2-row3
output 2 line grouped by row4-row5-row6-row7
output 3 line grouped by row8-row9

If you partition by column E, it will split the results into 7 groups. If you don't, it will group all 9 rows into one.

What is the rationale or rule that determines that the first 3 rows should be kept separate from the others, but as a single group? If you use only A,B,D there is nothing that distinguishes these 3 rows from the remaining 9. If you do use E, then these 3 rows are not one group, they are two groups because E has two values.

If you do want to partition by column E and experiment, all you need to do is to add that column in couple of places to Peso's query - see below:

;WITH cteSource(A, B, C, D,E, theGrp)
AS (
	SELECT	A,
		B,
		C,
		D,
		E,
		ROW_NUMBER() OVER (ORDER BY R) - ROW_NUMBER() OVER (PARTITION BY A, B, D,E ORDER BY R) AS theGrp
	FROM	@Sample
)
SELECT		A,
		B,
		D,
		E,
		MIN(C) AS theMin,
		MAX(C) AS theMax
FROM		cteSource
GROUP BY	A,
		B,
		D,
		E,
		theGrp
ORDER BY	A,
		B,
		MIN(C)

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 12:18:38

actually rule is

* select column e in (40,90)
* order by a,b,c
* group by a,b,d but follow column e
(
i mean in our sample (first sample or second sample)
row1,row2,row3 is one group - i need to get min(c) and max(c)
row4,row5,row6,row7 is another group - i need to get min(c) and max(c)
row8,row9 is another group - i need to get min(c) and max(c)
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-20 : 13:49:25

So what you are saying is that a row with a value for E column of 90 is the "end" of the group?

N 56°04'39.26"
E 12°55'05.63"

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-20 : 14:26:07

Yeah, 40 is starting, 90 is ending for group, but pls do not forget it might be more than one 40 or 90, in that case minimum of column c with value 40 is starting group, max of column c with value 90 will be ending group

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 15:14:09

Replace the calculation of the "theGrp" in Peso's code with what I have in red below:

;WITH cteSource(A, B, C, D, theGrp)
AS (
	SELECT	A,
		B,
		C,
		D,
		b.theGrp
	FROM
		@Sample a
		OUTER APPLY
		(
			SELECT MIN(R) AS theGrp
			FROM @Sample b
			WHERE b.R >= a.R
			AND EXISTS 
			(
				SELECT * FROM @Sample c
				WHERE c.R = b.R+1
				AND c.E < b.E
			)
		)b
)
SELECT		A,
		B,
		D,
		MIN(C) AS theMin,
		MAX(C) AS theMax
FROM		cteSource
GROUP BY	A,
		B,
		D,
		theGrp
ORDER BY	A,
		B,
		MIN(C)

alberto_pompeo
Starting Member

9 Posts

Posted - 2012-08-21 : 02:58:26

thanks for help
seems working. just a little bit slow. (takes 4mins) (more than 20K records like this)

i guess i will make temporary table.

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources