SQL help with MAX function and group by

From: <jeffchirco_at_gmail.com>
Date: 16 Nov 2005 17:11:14 -0800
Message-ID: <1132189873.991794.316300_at_o13g2000cwo.googlegroups.com>

[Quoted] I need help with a sql statment using max function. It should be simple but I am not seeing it.
Example:

EMP       DATE       GOAL
1	11/16/2005	5
1	11/15/2005	3
2	11/16/2005	8
3	11/15/2005	4
2	11/15/2005	9

I want to get the MAX(GOAL) per emp, and I want to display all fields. So I should see the results as

EMP       DATE       GOAL
1	11/16/2005	5
2	11/15/2005	9
3	11/15/2005	4

I can get the correct results without displaying the date by doing; select dbemp, MAX(dbnumber) FROM test
GROUP BY dbemp;

Its when I add the date field and have to group by it, it gets messed up.

Thanks for any help. Received on Thu Nov 17 2005 - 02:11:14 CET