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 2005 Forums

Transact-SQL (2005)

Group by

Author

Topic

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2010-05-29 : 05:09:17

Hi.
I have a table by 5 column. I want to group by first column and return second column of first row per group,Max of 3'rd column per group, Min of 4th column per group and 5th column of last row per Group. please help me how write this query?

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 05:24:12

[code]SELECT Col1, XXX, MAX(Col3), MIN(Col3), YYY
FROM MyTable
GROUP BY Col1[/code]

How do you define "First row per group" (XXX) and "Last row per group" (YYY)? That needs a definition for an Order By ...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-29 : 05:29:56

What do you mean my Second column of first row per group and 5th column of last row per Group ?
Please explain to help you...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2010-05-29 : 06:02:43

for example, by this table:
c1 c2 c3 c4 c5
------------------------------------
100 44 12 15 14
100 12 25 28 12
101 52 45 89 63
101 32 58 45 85
101 24 15 42 40

and run:

First(c2) per group,MAX(c3),Min(c4),Last(c5) per group
group by (c1)

must return:
100 44 25 15 12
101 52 58 42 40

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 06:18:00

Yeah, but that's the order you have put them in the example. There is NO order in a relational database - YOU have to provide a column that defines that sequence - either Date/Time, or an ID number, or something.

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2010-05-29 : 06:25:37

Yes, the column that I want group table by it is DateTime.
I want to group records by an interval, for example 10 minutes, 30 minutes and ...

I think this is true for it:

SELECT DATEDIFF(mi, 0, [DateTime]) / @Interval) * @Interval AS TID
FROM table
group by TID

Let to explain complete:
I have a table that store Open,High,Low and Close prices for symbols per minute.
I want to convert in to another timeframe like 30min,1hour, 6hour and...
for it, when I want to get 1Hour time frame prices in 2010-02-02 10:00, must return:

Open = Open where DateTime = 2010-02-02 10:00

High = MAX(High) where DateTime >= 2010-02-02 10:00 AND DateTime <= 2010-02-02 10:59

Low = MIN(Low) where DateTime >= 2010-02-02 10:00 AND DateTime <= 2010-02-02 10:59

Close = Close where DateTime = 2010-02-02 10:59

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 07:26:49

[code]
CREATE TABLE #TEMP
(
T_Date datetime,
T_C1 int,
T_C2 int,
T_C3 int,
T_C4 int,
T_C5 int
)

INSERT INTO #TEMP
SELECT '20000101', 100, 44, 12, 15, 14 UNION ALL
SELECT '20000102', 100, 12, 25, 28, 12 UNION ALL
SELECT '20000103', 101, 52, 45, 89, 63 UNION ALL
SELECT '20000104', 101, 32, 58, 45, 85 UNION ALL
SELECT '20000105', 101, 24, 15, 42, 40

SELECT T.T_C1,
[First C2] = A.T_C2,
Col3_MAX,
Col4_Min,
[Last C5] = D.T_C5
FROM
(
SELECT T_C1,
[Col3_MAX] = MAX(T_C3),
[Col4_Min] = Min(T_C4)
FROM #TEMP
GROUP BY T_C1
) AS T
JOIN
(
SELECT [T_RowNumber_ASC] = ROW_NUMBER()
OVER
(
PARTITION BY T_C1
ORDER BY T_C1, T_Date
),
X.*
FROM
(
SELECT T_Date,
T_C1,
T_C2
FROM #TEMP
WHERE 1=1
) AS X
) AS A
ON A.T_C1 = T.T_C1
AND A.T_RowNumber_ASC = 1
JOIN
(
SELECT [T_RowNumber_DESC] = ROW_NUMBER()
OVER
(
PARTITION BY T_C1
ORDER BY T_C1, T_Date DESC
),
Y.*
FROM
(
SELECT T_Date,
T_C1,
T_C5
FROM #TEMP
WHERE 1=1
) AS Y
) AS D
ON D.T_C1 = T.T_C1
AND D.T_RowNumber_DESC = 1
ORDER BY T_C1
[/code]

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2010-05-29 : 07:54:55

Thanks very much. But its run time is much, notice that I want to execute this query about 300 time per minute and in first second of minute!!!

See it,I think it's better to use DateTime Col in where clause to get data range.


declare @Interval int = 120  --i.e: For 2Hours time frame
declare @Symbol nvarchar(10) = 'AUDCAD'
declare @dt datetime = '2010-04-01 00:00'


declare @edt datetime
set @edt = DATEADD(mi,@Interval,@dt)

select   ROW_NUMBER() OVER (PARTITION BY DATEADD(mi, (DATEDIFF(mi, 0, [DateTime]) / @Interval) * @Interval, 0)
	                      ORDER BY [DateTime]) AS Seq, 
	                      [Open], [Close], [High], [Low]  from HlocData
 WHERE (Symbol = @Symbol) AND (DateTime>=@dt)AND (DateTime<@edt)

Only remain select MAX,Min,Open where Seq=1, Close where Seq = @Interval.
I have problem in last select.

Thanks again for your help Kristen!

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 12:11:09

Sure, you can put a constraint in the WHERE clause, and that will make the number of records processed much smaller.

"PARTITION BY DATEADD(mi, (DATEDIFF(mi, 0, [DateTime]) / @Interval) * @Interval, 0)" will be very slow - i.e. having that Function in the PARTITION clause (or WHERE or JOIN or PARTITION / PARTITION's ORDER BY)

Sachin.Nand

2937 Posts

Posted - 2010-05-29 : 16:30:46

I think an Unpivot will perform much better in these kind of queries and are easily maintainable.


CREATE TABLE #TEMP
(
	T_Date	datetime,
	T_C1	int,
	T_C2	int,
	T_C3	int,
	T_C4	int,
	T_C5	int
) 

INSERT INTO #TEMP
SELECT '20000101', 100, 44, 12, 15, 14 UNION ALL
SELECT '20000102', 100, 12, 25, 28, 12 UNION ALL
SELECT '20000103', 101, 52, 45, 89, 63 UNION ALL
SELECT '20000104', 101, 32, 58, 45, 85 UNION ALL
SELECT '20000105', 101, 24, 15, 42, 40 


Select 
T_C1,
MAX(Case When columns='T_C2' Then col End)[First C2],
MAX(Case When columns='T_C3' Then col End)Col3_MAX,
MIN(Case When columns='T_C4' Then col End)Col4_Min,
MIN(Case When columns='T_C5' Then col End)[Last C5]
From
(Select * From #TEMP)u
UnPivot
(col for columns In(t_c2,t_c3,t_c4,t_c5))v Group by T_C1


Drop table #temp

PBUH

Kristen
Test

22859 Posts

Posted - 2010-05-30 : 02:14:27

However, if I've understood the requirement correctly, that won't work for data just as:


INSERT INTO #TEMP
SELECT '20000101', 100, 44, 12, 15, 14 UNION ALL
SELECT '20000102', 100, 01, 25, 28, 01 UNION ALL
SELECT '20000103', 100, 99, 25, 28, 99 UNION ALL
SELECT '20000104', 100, 12, 25, 28, 12 UNION ALL
SELECT '20000105', 101, 52, 45, 89, 63 UNION ALL
SELECT '20000106', 101, 32, 58, 45, 85 UNION ALL
SELECT '20000107', 101, 24, 15, 42, 40

which I don't think should change the desired outcome

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 03:31:50

quote:
Originally posted by Kristen

However, if I've understood the requirement correctly, that won't work for data just as:

INSERT INTO #TEMP
SELECT '20000101', 100, 44, 12, 15, 14 UNION ALL
SELECT '20000102', 100, 01, 25, 28, 01 UNION ALL
SELECT '20000103', 100, 99, 25, 28, 99 UNION ALL
SELECT '20000104', 100, 12, 25, 28, 12 UNION ALL
SELECT '20000105', 101, 52, 45, 89, 63 UNION ALL
SELECT '20000106', 101, 32, 58, 45, 85 UNION ALL
SELECT '20000107', 101, 24, 15, 42, 40

which I don't think should change the desired outcome

Well it does get a little tweaky with that data.But I think I got what the Op wanted.


CREATE TABLE #TEMP
(
	T_Date	datetime,
	T_C1	int,
	T_C2	int,
	T_C3	int,
	T_C4	int,
	T_C5	int
) 

INSERT INTO #TEMP
SELECT '20000101', 100, 44, 12, 15, 14 UNION ALL
SELECT '20000102', 100, 01, 25, 28, 01 UNION ALL
SELECT '20000103', 100, 99, 25, 28, 99 UNION ALL
SELECT '20000104', 100, 12, 25, 28, 12 UNION ALL
SELECT '20000105', 101, 52, 45, 89, 63 UNION ALL
SELECT '20000106', 101, 32, 58, 45, 85 UNION ALL
SELECT '20000107', 101, 24, 15, 42, 40 

Select 
T_C1,
MAX(Case When [First C2 rank]=1 Then T_C2 End)[First C2],
MAX(Case When columns='T_C3' Then col End)Col3_MAX,
MIN(Case When columns='T_C4' Then col End)Col4_Min,
MAX(Case When [Last C5 rank]=1 Then T_C5 End)[Last C5]
From
(
Select 
 *,
Dense_rank()over(Partition by T_C1 Order by T_Date)[First C2 rank],
Dense_rank()over(Partition by T_C1 Order by T_Date desc)[Last C5 rank]
From
(Select * From #TEMP)u
UnPivot
(col for columns In(t_c3,t_c4))v 
)T group by T_C1


Drop table #temp

PBUH

charles1812
Starting Member

1 Post

Posted - 2011-04-25 : 03:13:55

someone discuss about group by in http://www.longhowl.com/howls/137

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources