group by period of time

Question

group by period of time

Nili S

Right there with Babe

Points: 795
More actions
June 24, 2007 at 9:23 am

#174195

Hello,
I have a table containing large data, and one of the fields is a date field. Every minute there are hundreds of rows in the table.
I want to query only data that was inserted last every, lets say, 10 minutes, meaning: query one row per period of 10 minutes.
How do I group by a period of time, so that I can get the max(date) for each 10 minutes? Can it be done using simple SQL query (since I need for it to work on both Oracle and SQL Server DB) or do I have to use a TSQL/PLSQL function?
Thanks.
Nili.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

First, it really ticks folks off when you double post like you did... this is one place where you do need to put all your eggs in one basket... keeps folks from working on a solution on one post when you already have received one on the other. Here's your other post...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=376139

Also, you haven't given much in the line of information... does the table have a primary key? If so, what's it's name and data type? Is it an identity column? A trip to the following URL will tell you what most of us expect when you post on this forum... the "Help us help you" section is pretty important... you should read it carefully before the next post... it'll help you get very good answers in a very short period of time instead of having folks guessing like I have to because of your short post.

http://www.sqlservercentral.com/columnists/siyer/2645.asp

To answer your second question first, the following will return non-keyed max dated rows for every 10 minute time slot... you'll need to play with indexes to make it perform quickly but it will do the whole table. The caveat is, because you didn't identify what the PK of the table is, if multiple rows tie for max date/time for each 10 minute slot, all those rows will be returned as part of the slot. Obviously, you will need to make substitutions for "yourtable" and "yourdatecolumn".

SELECT main.*

FROM dbo.yourtable main,

(

SELECT MAX(yourdatecolumn) AS MaxSlottedDate,DATEDIFF(mi,0,yourdatecolumn)/10 AS TenSlot

FROM dbo.yourtable

GROUP BY DATEDIFF(mi,0,yourdatecolumn)/10

) slot

WHERE main.yourdatecolumn = slot.MaxSlottedDate

ORDER BY main.yourdatecolumn

For your first question of returning the single max row for the last ten minutes, that would simply be the last row in the table IF the datetime column is autogenerating. If it's not autogenerating, you need to tell us that, maybe include the table schema, and 20 rows of sample data setup as INSERT/SELECTs so we can do some testing before replying.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nili S Right there with Babe Points: 795 More actions · Answer 2

Thank you, Jeff. This is exactly what I needed. This helps a lot.

I see I have managed to annoy you. But believe me- I had no intention to... 🙂 I thought posting the question in 2 forums it might fit into would be helpful rather than disturbing. Anyway- I only had one question and you have given me the answer so thanks a lot.

Nili.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Heh... it wouldn't be just me that gets annoyed... some folks get downright rude about cross posting.

Anyway, glad to help and thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)