Max of a field in a query - 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 of a field in a query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-21 : 10:23:08

Rod writes "I have three tables in my query. In the first table I have the Client info and the second, the school history info and the third has the school info. I'm trying to get the max of the Effective Date so I can get the last school this client has attended.

When I run the query, if the client has only had one school, no prob, but if a client has multiple schools it will pick up all of them.

Here's a copy of my SQL.

SELECT dbo.Client.ClientID, dbo.Client.ClientLastName, dbo.Client.ClientFirstName, MAX(dbo.ClientSchoolHistory.EffectiveDate) AS maxEffDate,
dbo.School.SchoolName
FROM dbo.Client INNER JOIN
dbo.ClientSchoolHistory ON dbo.Client.NewClientID = dbo.ClientSchoolHistory.NewClientID INNER JOIN
dbo.School ON dbo.ClientSchoolHistory.SchoolID = dbo.School.SchoolID
GROUP BY dbo.Client.ClientID, dbo.Client.ClientLastName, dbo.Client.ClientFirstName, dbo.School.SchoolName"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-21 : 10:47:48

Double derived tables

SELECT	y.ClientID	
	, y.ClientLastName
	, y.ClientFirstName
	, x.maxEffDate
	, z.SchoolName

FROM	dbo.Client y INNER JOIN 
	(
	SELECT	a.NewClientID
		, a.SchoolID
		, b.maxEffDate
	FROM	dbo.ClientSchoolHistory a INNER JOIN
		(
		SELECT	NewClientID
			, MAX(EffectiveDate) AS maxEffDate
		FROM	dbo.ClientSchoolHistory
		GROUP BY NewClientID
		) b 
                    ON a.NewClientID = b.NewClientID
		    AND a.EffectiveDate = b.maxEffDate
	) x ON y.NewClientID = x.NewClientID
	INNER JOIN dbo.School z
	  ON x.SchoolID = z.SchoolID

Rod Aubertin
Starting Member

1 Post

Posted - 2004-04-23 : 10:18:39

I gave it a try and works fantastic.

I'm so used to working with queries in Access, which means I have to relearn SQL again. Just like riding a bike.

Rod

Rod Aubertin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources