pulling maximum date faster - SQL Server Forums

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)

pulling maximum date faster

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-07-10 : 11:15:54

Is there a way to make this query more efficient?
#DATES holds a universe of company codes.

I want to populate the most recent date for pricing.

UPDATE #DATES
SET MAXDATE = (
SELECT MAX(A.DATE_)
FROM DAILY_PRICES A
JOIN #DATES B
ON A.CODE = B.CODE)

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-07-10 : 11:32:15

[code]
UPDATE B SET B.MAXDATE = MAX(A.DATE)
FROM DAILY_PRICES A INNER JOIN #DATES B
ON A.CODE = B.CODE
[/code]
Will this help?

Thanks
Karunakaran

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-10 : 11:42:38

pug2694328 - does your query actually work (i.e. achieve the results you desire)? No point just making it more efficient if it doesn't.

karuna - that seems to give an error: 'An aggregate may not appear in the set list of an UPDATE statement.'

Well, maybe one of these is what's needed?...

update a set MAXDATE = b.MAXDATE
from #DATES a inner join (
select code, max(DATE_) MAXDATE from DAILY_PRICES group by CODE) b on a.CODE = b.CODE


update a set MAXDATE = (select max(DATE_) from DAILY_PRICES where CODE = a.CODE) from #DATES a

Once you have a number of possible solutions, you'll need to test them yourself to see which is more efficient.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-07-10 : 11:44:37

Thanks so much for the help with this one.
Perhaps I'm doing it wrong, but I hit an error when using either of the queries.

Server: Msg 157, Level 15, State 1, Line 1
An aggregate may not appear in the set list of an UPDATE statement.

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-07-10 : 11:46:30

quote:
Originally posted by RyanRandall

karuna - that seems to give an error: 'An aggregate may not appear in the set list of an UPDATE statement.'

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Many time I write this kind of query, get that error and rewrite the query...And I still manage to forget that error...

Too Bad...

Thanks
Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources