Aggregate Function

[crashevans]

Hi all

I get the following error..

You tried to execute a query that does not include the specified expression 'Mark' as part of an aggregate function

Code - SELECT TestID, Max(DateCompleted), Mark FROM UserTest WHERE ServiceID = 'G000000G' GROUP BY TestID;

However I dont want to group by mark as well because it shows results I dont want! But I want it to show the latest entry (DateCompleted) its appropriate TestID and its Mark.

Any ideas? Thanks in advance.

 

[bborissov]

SELECT UserTest.TestID,
       UserTest.DateCompleted,
       UserTest.Mark
FROM UserTest
INNER JOIN (SELECT TestId, MAX(DateCompleted) AS DateCompleted
                   FROM UserTest
                   WHERE ServiceID = 'G000000G'
                   GROUP BY TestID) Tbl1
ON UserTest.TestId        = Tbl1.TestId AND
   UserTest.DateCompleted = Tbl1.DateCompleted

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP