First value in every group combined with datetime

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)

First value in every group combined with datetime

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-28 : 04:40:53

How do i get the first value from every group in a table?
I also want to group by every 10 minute

for example
[LogTime] ; [Value]
2008-01-01 00:03:00 1
2008-01-01 00:06:00 2
2008-01-01 00:09:00 3
2008-01-01 00:12:00 4
2008-01-01 00:15:00 5
2008-01-01 00:18:00 6
2008-01-01 00:20:00 7

would give something like
[LogTime] ; [Value]
2008-01-01 00:00:00 1
2008-01-01 00:10:00 3 <-- i guess
2008-01-01 00:20:00 7

I have already begin but the problem remain, i
can't find the "first" value of every group

SELECT MIN(DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', ([LogTime])) / 10 * 10, '19000101 00:10')) AS [LogTime], MAX([Value])
FROM #temp
GROUP BY (DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', ([LogTime])) / 10 * 10, '19000101 00:10'))
ORDER BY [LogTime]

Im using MAX([Value]) here
but that would have done better with and FIRST([Value])

Regards
V

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 05:39:53

[code]
DECLARE @data TABLE
(
[LogTime] datetime,
[Value] int
)
INSERT INTO @data
SELECT '2008-01-01 00:03:00', 1 UNION ALL
SELECT '2008-01-01 00:06:00', 2 UNION ALL
SELECT '2008-01-01 00:09:00', 3 UNION ALL
SELECT '2008-01-01 00:12:00', 4 UNION ALL
SELECT '2008-01-01 00:15:00', 5 UNION ALL
SELECT '2008-01-01 00:18:00', 6 UNION ALL
SELECT '2008-01-01 00:20:00', 7

SELECT LogTime, Value
FROM
(
SELECT *,
row_no = row_number()
OVER
(
PARTITION BY DATEDIFF(minute, DATEADD(DAY, DATEDIFF(DAY, 0, LogTime), 0), LogTime) / 10
ORDER BY LogTime
)
FROM @data
) d
WHERE d.row_no = 1

/*
LogTime Value
------------------------- -----------
2008-01-01 00:03:00.000 1
2008-01-01 00:12:00.000 4
2008-01-01 00:20:00.000 7

(3 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 06:42:33

This partition by will suffice

PARTITION BY	DATEDIFF(minute, 0, LogTime) / 10

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

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-28 : 15:44:43

It looks to work just fine here.
I really have a good hint now how to move on.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources