Selecting the most recent values for a day

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

General SQL Server Forums

New to SQL Server Programming

Selecting the most recent values for a day

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2009-11-09 : 13:37:48

I have a table which has the results from various runs for an analysis. The tabel stores the rundate as datetime, with the analysis results.

Some days the analysis will run multiple times and I need to get the most recent analysis result for each day. I have tried using the following, but if there are multiple loss values on a given date then it pulls both values.

SELECT distinct (convert(VarChar(10),RunDate,111)) as Rundate,
		cast(Sum(Loss)/1e6 as int) as Loss
FROM tbl_Analysis
group by rundate
order by rundate desc

How can I change the above to get just the most recent result on a date?

Thx

LH

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 13:59:11

Well if you want the last one, then why are you doing a GROUP BY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lionheart
Starting Member

41 Posts

Posted - 2009-11-09 : 14:08:48

Because it gives an error message if I do not. I am sure that if the code were changed to how I would like the results it would not need it. I am thinking that maybe I need to create a temp table with the last datetime for each date and then join by that, but how do I select the last datetime for any given date and put into a table?

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 14:14:39

You just want 1 row...there's no other key?


SELECT * 
  FROM tbl_Analysis o
 WHERE EXISTS ( SELECT * FROM tbl_Analysis i
				 HAVING MAX(i.rundate) = o.rundate)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources