Select last 30 rows of each group

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

SQL Server Development (2000)

Select last 30 rows of each group

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-28 : 13:58:12

yy writes "Hi,
I have a table called MIN2ANA with 3 columns, namely 'point','time','value' all of datatype varchar.

The Point column records the name of several distinct points and Value column records the value of that point at a particular minute (recorded under Time column).
There will be an entry for each distinct point every minute.
e.g
|Point | Time | Value |
|wcgh | 2004-04-20 16:24:00 | 0.36 |
|wcgh | 2004-04-20 16:25:00 | 0.56 |
:
:

May i know how the selection code should be if i would like to select the last 30 minutes 's rows of data for each distinct point?

Thank you =)"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 14:09:36

select *
from tbl
where time > dateadd(mm,-30,(select max(t2.Time) from tbl t2 where t2.Point = t1.Point))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yy
Starting Member

5 Posts

Posted - 2004-04-28 : 22:59:04

Thx for helping, nr =)
Tried the code u posted, but it returns an error message saying: "DATEADD": invalid identifier

is it something to do with my datatype for column Time? i've used Varchar for the 'Time' column...

Pls advise...
A million thanx!~

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-28 : 23:59:35

select *
from tbl
where time > dateadd(mm,-30,(select max(cast(t2.Time as datetime)) from tbl t2 where t2.Point = t1.Point))

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

yy
Starting Member

5 Posts

Posted - 2004-04-29 : 22:35:35

Hi derrickleggett,

Thx a lot for the code, but it still returns the same error of :"DATEADD": invalid identifier... *sigh* ...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-29 : 23:24:38

Can you even do this?

SELECT CAST(Time AS DATETIME) FROM tbl

or

SELECT CAST(Time + '.000' AS DATETIME) FROM tbl

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

yy
Starting Member

5 Posts

Posted - 2004-04-29 : 23:39:28

Nope...both returns invalid datatype for the part DATETIME...

Actually what i really need is to return the 30 rows counting up from the row(s) with the maximum time in the column 'Time', regardless of what is the value of the other 2 columns...
is there any way i can achieve this?

Thx...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-30 : 01:35:28

Are you using sql server?
Can you paste the actual error message.
If time had invalid entries you should get an out of range error.
If it was a bad datatype you should get an unable to convert error.
Sounds like it is complaining about datetime itself.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yy
Starting Member

5 Posts

Posted - 2004-04-30 : 03:04:38

don't think so...i'm using Oracle SQL*Plus.

Here's the error i've got:

SQL> select cast(Time +'.000' AS DATETIME) from min4ANA;
select cast(Time +'.000' AS DATETIME) from min4ANA
*
ERROR at line 1:
ORA-00902: invalid datatype

is there any way i can just ask it to return the 30 rows counting up from those rows with the maximum time for 'Time' column?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-30 : 03:24:22

A few options here. Get sql server, post on an oracle forum... :)

Think the oracle function is to_date. Think you can use dateadd but think the identifiers are different (actually my post should have been mi not mm for minutes instead of months).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yy
Starting Member

5 Posts

Posted - 2004-04-30 : 03:56:32

oic...i'm very new to sql so didn't know there's a different between the code for sql server and those for oracle's... haha
then maybe i need to post on an oracle forum instead..
but really thanks a lot for your help

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-30 : 08:48:52

www.dbforums.com has a pretty active Oracle forums just so you know. You could also subscribe to lazydba.com and post there.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources