Max Date by Identifier - 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

Transact-SQL (2000)

Max Date by Identifier

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2006-05-30 : 12:17:19

I have a table tblBudget_Data

in this table I have thousands of rows with many fields including fields: ORGID, LastModified, LastModifiedBy

I am trying to obtain a query that displays the greatest date/time which is stamped in LastModified and LastModifiedBY which is an ID field for a user by ORGID.

There are many records per ORGID, Basically I want to identify that last time a record for that orgid was updated.

My results would be something like this:

OrgID LastModified LastModifiedBY
61 2006-05-16 13:52:06.000 997
67 2006-05-14 10:52:06.000 325
98 2006-05-16 05:52:06.000 326
102 2006-03-16 23:52:06.000 369
105 2006-05-13 16:52:06.000 125
119 2006-05-12 18:52:06.000 986
201 2006-04-16 03:52:06.000 365

I have this and only get the LastModified record, 1 record

SELECT OrGID, LastModified, LastModifiedBy
FROM tblBudgetData
WHERE LastModifiedBy IS NOT NULL
AND LastModified IN (SELECT MAX (LastModified)
FROM tblBudget_Data
WHERE BudgetYear = 2006)

returns

OrgID LastModified LastModifiedBY

61 2006-05-16 13:52:06.000 997

Also, I have an ASP page that runs slowly running this now. It pulls in the last by ORGID, but makes return trips to the server each time to grab the info by ORGID, It then runs another select to obtain the user information. I am trying to figure out a way to speed up the process. I thought maybe converting half to a stored procedure would help?

Any ideas or insight you may have would be appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-30 : 12:36:17

Here are 2 (untested) ways, see if they work and which one is faster for you:


select	orgid
	,lastModified
	,LastModifiedBy
from	tblBudget_Data bd
where	lastModified = (select max(lastModified) from tblBudget_Data where orgid = bd.orgid)


select	b.orgid
	,b.lastModified
	,b.LastModifiedBy
from	(
	select	OrgID
		,max(LastModified) LastModified
	from	tblBudget_Data
	group by orgID
	) a
join	tblBudget_Data b
		on b.orgid = a.orgid
		and b.LastModified = a.LastModified

Be One with the Optimizer
TG

JimAmigo
Posting Yak Master

119 Posts

Posted - 2006-05-30 : 13:33:08

Each worked great. Now if I can figure out how to tie them together to the ASP page. :) THanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources