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)
 grabbing most recent row of data in time series

Author  Topic 

cr8nk
Yak Posting Veteran

77 Posts
Posted - 2001-12-28 : 20:47:40
I have a table that is populated with account information. This table can have multiple rows inserted for the same account. The primary key is set on the account number and the time_stamp fields. I am wondering what the best way to grab the last row inserted for that account is.

Here is the table and my query attempts.

create table daily_report_app
(
acct_num varchar(10),
time_stamp int,
result varchar(5)
)

I am interested in grabbing the last result entered for that account.

Here is my query so far.

select a.acct_num,a.time_stamp,a.result
from daily_report_app a,daily_report_app b
where a.time_stamp = (select max(b.time_stamp)
from daily_report_app_hauto b where a.acct_num = b.acct_num)
group by a.acct_num,a.time_stamp,a.result

This query takes quite a while to execute and I believe there has to be a better way to grab the last row of data in a time series.

thanks,

Cr8nk

Nazim
A custom title

1408 Posts
Posted - 2001-12-28 : 23:58:54
how about this one.

select acct_num,time_stamp,result
from daily_report_app
group by acc_num,time_stamp,result
having time_stamp=max(time_stamp)


HTH

-------------------------
Graz's Baby is my Master:)
Go to Top of Page

aclarke
Posting Yak Master

133 Posts
Posted - 2001-12-29 : 01:41:43
I'm having a similar problem. BTW I posted it at http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11664 without much success ...

Nazim, seems that what you've posted would just return the whole table. A simpler way to do that would be "select * from daily_report_app". ha ha. Anyways, what if I wanted to get the acct_num, time_stamp and result for each acct_num, where the time_stamp was the greatest for that acct_num? Essentially picking out a list of the "latest entry" acct_nums.

cr8nk: Not sure of the syntax of your example above. What's "daily_report_app_hauto" in there? Typo or something undefined?
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2001-12-29 : 02:31:38
Oops! Am sorry. Thanx aClarke for pointing out the BLUNDER .

how about this one, am not sure whether this will do it.

select acct_num,time_stamp,result
from daily_report_app a
inner jon
(
select acct_num,max(time_stamp)
from daily_report_app
group by acct_num ) b
on a.acct_num=b.acct_num and a.time_stamp=b.time_stamp


c8lnk , does your table has a primary key or a identity field with it.
and i tried your query and it doesnt works out correctly too.





-------------------------
Graz's Baby is my Master:)

Edited by - Nazim on 12/29/2001 02:46:15
Go to Top of Page

aclarke
Posting Yak Master

133 Posts
Posted - 2001-12-29 : 02:47:00
Nazim: I'm still confused. If you group on time_stamp, then use "having time_stamp=max(time_stamp), your groups from which you're taking each max() includes each time_stamp, therefore this distills down to "where time_stamp=time_stamp". Am I missing something? I tried out a parallel query here and that's what I get.
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2001-12-29 : 02:53:28
aClarke i realized it too, and felt something is fishy about it, and 45 secs before your posting i had edited it. have a look on the edited post.

will this work???

-------------------------
Graz's Baby is my Master:)
Go to Top of Page

aclarke
Posting Yak Master

133 Posts
Posted - 2001-12-29 : 03:16:37
ALL RIGHT!!! Thank you Nazim!!!! This works great, and as always, I wonder how I could have missed doing that myself...not to in any way rain upon your glorious intellectual prowess ;-) In case you're interested, I'll post a result to my own problem to http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11664
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2001-12-29 : 03:54:03
Wow! it makes me feel happy that i could help this intelligent 1:).

you are always welcome Clarke.

i just followed your link, its pretty neatly done. i liked the way you have posted , with script.



-------------------------
Graz's Baby is my Master:)
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2001-12-30 : 02:56:20
Do you want the last ROW or the last group? Is this for a single account number, or the last one for EACH account number? If you just want the last row inserted for a given account number, why not do something like...

SELECT TOP 1 acct_num, time_stamp, result
FROM daily_report_app
WHERE acct_num = @AccountNumber -- passed in value
ORDER BY time_stamp DESC


--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page

aclarke
Posting Yak Master

133 Posts
Posted - 2001-12-30 : 03:03:04
Wait, umm, someone's actually answering the ORIGINAL question? *ahem*

Thanks for the focus !
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2001-12-30 : 03:06:03
that was a pretty simple solution Mark!

About my solution . i think a distinct clause should help it weed out duplicate records.

select distinct acct_num,time_stamp,result
from daily_report_app a
inner jon
(
select acct_num,max(time_stamp)
from daily_report_app
group by acct_num ) b
on a.acct_num=b.acct_num and a.time_stamp=b.time_stamp

-------------------------
Graz's Baby is my Master:)
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2001-12-30 : 03:16:23
quote:

Wait, umm, someone's actually answering the ORIGINAL question? *ahem*


Yeah, that happens sometimes...

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2001-12-30 : 03:20:53
was wondering wherez cr8nk in all this. poor guy, never bothered to put a line after posting it. for me it looked as though it was Clarke's post.



-------------------------
Graz's Baby is my Master:)
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2001-12-31 : 07:35:36
Ajarn on second look , your query doenst do what Cr8nk wants

quote:

SELECT TOP 1 acct_num, time_stamp, result
FROM daily_report_app
WHERE acct_num = @AccountNumber -- passed in value
ORDER BY time_stamp DESC


this is his query
quote:

select a.acct_num,a.time_stamp,a.result
from daily_report_app a,daily_report_app b
where a.time_stamp = (select max(b.time_stamp)
from daily_report_app_hauto b where a.acct_num = b.acct_num)
group by a.acct_num,a.time_stamp,a.result


i think what he wants my query does it.

select distinct acct_num,time_stamp,result
from daily_report_app a
inner jon
(
select acct_num,max(time_stamp)
from daily_report_app
group by acct_num ) b
on a.acct_num=b.acct_num and a.time_stamp=b.time_stamp
Go to Top of Page

cr8nk
Yak Posting Veteran

77 Posts
Posted - 2001-12-31 : 14:54:50
I am glad my topic generated a lot of discussion. This last query by Nazim works a lot faster than mine.

select distinct acct_num,time_stamp,result
from daily_report_app a
inner join
(
select acct_num,max(time_stamp)
from daily_report_app
group by acct_num ) b
on a.acct_num=b.acct_num and a.time_stamp=b.time_stamp


Thanks for the help.

-Cr8nk
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site