incorrect MAX result with query. (diagram photo included)

[Peter Primrose]

Hi all,
I have the following SQL statement that returns the table-photo below:

 
SELECT BE.note, BE.note_date, BE.type_2, BE.status_2, BE.user_ID, CB.entity_id
 
FROM buyer_empl AS BE, case_buyer AS CB
 
WHERE 
BE.buyer_case_ID=CB.buyer_case_ID
AND CB.case_ID=332

I would like to get the *latest* note from each entity_id from the table (the resulted query).
For this I did:

    
SELECT MAX(note_date),entity_ID, note, note_date, type_2, status_2, user_id
 FROM 
(SELECT BE.note, BE.note_date, BE.type_2, BE.status_2, BE.user_ID, CB.entity_id
 
FROM buyer_empl AS BE, case_buyer AS CB
 
WHERE 
BE.buyer_case_ID=CB.buyer_case_ID
AND CB.case_ID=332) AS myView GROUP BY entity_id

but the results are incorrect. the max(note_date) is correct but not the note?!?!?!
any idea what am I doing wrong?

thanks

[Scott Selikoff]

All records in a query result set must be uniform in type. Therefore, your combination "SELECT MAX(note_date),..." will not work since it should return the max note date and all records joined with in. If you want a single record you need to indicate that such as "select record where note_date = (select max(note_date))".

[Peter Primrose]

yep....thank you!