Hi Folks,

Say I have data like this:

ID   Name   TestResult
1     Bill       20
2     Bill       22
3     Bill       34
4     Jim      11
5     Jim      14
6     Jim      25

I want to pick out the _last_entered_ results for Bill and Jim. ID increments
automatically, so the MAX ID for Bill and Jim will be their last test result.

However SQL:

SELECT MAX(ID), Name, TestResult

FROM TestResults
GROUP BY Name

Gives the following result:

3  Bill 20
6 Jim 11

i.e. it returns the test results for tests 1 and 4, not 3 and 6.

--

I realise I can get the result with a second query to create a memory table in the form:

SELECT MAX(ID) as ID, Name

INTO MEMORY\MaxTests

FROM TestResults

GROUP BY ID

;

SELECT ID, Name, TestResult

FROM TestResults

WHERE ID IN

(SELECT ID FROM MEMORY\MaxTests)


--

This works, but am I missing an easier way of writing it?
Or ... is there a better way to think about the data in the first place?

"adam" <adam@fullwellmill.co.uk> wrote in message
news:26D45989-779E-4F69-939B-89F9205EE91A@news.elevatesoft.com...
> Hi Folks,
>
> Say I have data like this:
>
> ID   Name   TestResult
> 1     Bill       20
> 2     Bill       22
> 3     Bill       34
> 4     Jim      11
> 5     Jim      14
> 6     Jim      25
>
> I want to pick out the _last_entered_ results for Bill and Jim. ID
> increments
> automatically, so the MAX ID for Bill and Jim will be their last test
> result.
>
> However SQL:
>
> SELECT MAX(ID), Name, TestResult
>
> FROM TestResults
> GROUP BY Name
>
> Gives the following result:
>
> 3  Bill 20
> 6 Jim 11
>
> i.e. it returns the test results for tests 1 and 4, not 3 and 6.
>
> --
>
> I realise I can get the result with a second query to create a memory
> table in the form:
>
> SELECT MAX(ID) as ID, Name
>
> INTO MEMORY\MaxTests
>
> FROM TestResults
>
> GROUP BY ID   <<<<?????
>
> ;
>
> SELECT ID, Name, TestResult
>
> FROM TestResults
>
> WHERE ID IN
>
> (SELECT ID FROM MEMORY\MaxTests)
>
>
> --
>
> This works, but am I missing an easier way of writing it?
> Or ... is there a better way to think about the data in the first place?
>

Even if you get it to work on a single query, there is no guarantee it will
always work in the future. You should only select the aggregate fields and
the group by fields in the query. That's it.

Also, your GROUP BY is wrong. Try this

SELECT MAX(ID) as MID, Name
INTO MEMORY\MaxTests
FROM TestResults
GROUP BY Name;
CREATE INDEX ByID ON Memory\MaxTests (MID);
SELECT ID, Name, TestResult
FROM TestResults
JOIN Memory\MaxTests
ON (MID = ID);

Why do you duplicate the name field in all rows?

Robert