Yet another GROUP BY question - 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)

Yet another GROUP BY question

Author

Topic

Backlash
Starting Member

4 Posts

Posted - 2007-02-08 : 13:31:33

I am struggling a little bit with my query (actually a view), which contains a GROUP BY clause to eliminate some duplication and perform some aggregate functions.

My basic query is like this:


SELECT 
  pid.Value AS MapVersion,
  pi.Folio AS ArticleTitle,
  a.[Name] AS ActivityName,
  pid2.Value AS ArticleType,
  MIN(ai.StartDate) AS StartDate,
  MAX(ai.FinishDate) AS FinishDate, 
  pi.ID AS ProcessId,
  aid.[User] AS Contributor

FROM  _ProcInst pi 
  INNER JOIN _ProcInstData pid ON pid.ProcInstID = pi.ID AND pid.Name = 'MapVersion' 
  INNER JOIN _ProcInstData pid2 ON pid2.ProcInstID = pi.ID AND pid2.Name = 'ArticleType' 
  INNER JOIN _Proc p ON pi.ProcID = p.ID
  INNER JOIN _ProcSet ps ON p.ProcSetID = ps.ID
  INNER JOIN _ActInst ai ON ai.ProcInstID = pi.ID
  INNER JOIN _Act a ON ai.ActID = a.ID
  INNER JOIN _ActInstDest aid ON aid.ActInstID = ai.ID AND aid.ProcInstId = pi.ID

GROUP BY pid.Value, pid2.Value, pi.Folio, a.[Name], pi.ID, aid.[User]

(not my table names/schema, by the way)

This works fine. However, I do not want to Group By aid.User anymore. Instead, I want to get the aid.User value that corresponds to the MAX(ai.FinishDate).

In general terms, how can I get a piece of data from the row that my aggregate function is fetching?

Thanks!

cognos79
Posting Yak Master

241 Posts

Posted - 2007-02-08 : 15:17:14

write a function that takes in primary key value from aid table and returns user value for tht record

SELECT
fn_uservalue(aid.actinstid)
....rest of select parameters

Backlash
Starting Member

4 Posts

Posted - 2007-02-08 : 17:11:44

That would work I think (although that amounts to a correlated subquery which I hoped to avoid). However, the problem isn't getting the user value once I have the pk from the aid table. The problem is getting the pk for the aid table that corresponds to the MAX FinishDate in the ai table for a particular ProcInstID and ActID.

That is, I want the MAX FinishDate and the pk from the ai table when grouping the ai table on ProcInstID and ActID.

Backlash
Starting Member

4 Posts

Posted - 2007-02-08 : 17:49:10

Well, I got it working (I think) by using a function, as suggested. Thanks for the suggestion. My function is defined as


	DECLARE @Ret VARCHAR(100)

	SELECT @Ret = aid.[User]
	FROM _ActInst ai
	INNER JOIN
		(SELECT pi.ID AS ProcessID,
			MAX(ai.FinishDate) AS MaxDate,
			ai.ActID
		 FROM dbo._ProcInst pi
			INNER JOIN dbo._ActInst ai ON ai.ProcInstID = pi.ID
		 GROUP BY pi.ID, ai.ActID) dates
	    ON dates.ProcessId = ai.ProcInstID AND dates.ActID = ai.ActID AND dates.MaxDate = ai.FinishDate
	INNER JOIN _actInstDest aid ON aid.ActInstID = ai.ID AND aid.ProcInstId = ai.ProcInstID AND aid.status = 2 

	WHERE ai.ProcInstID = @ProcInstID
	AND ai.ActID = @ActID


	RETURN @Ret

However, since it calls this function for every row in my query, the performance will start to suck once the database gets large. Does anyone know how to do this another way.

Backlash
Starting Member

4 Posts

Posted - 2007-02-13 : 12:03:49

In case anyone was wondering (though it doesn't look like it), I answered my own question today. All I did was turn that function into a view by removing the WHERE clause. I then just joined to this view on my two former params (@ProcInstID and @ActID) in my main query. It seems so obvious in retrospect, not sure why I didn't think of that last week.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources