Select from a group based on greatest date

microsoft.public.sqlserver.programming

2 May 2007 3:15 PM

dwopffl

I need some query help

I have the following table

ENTITY ID            ENC #              DATE
1044                    101                  05/02/2007
1044                    101                  05/01/2007
1044                    101                  04/30/2007
1044                    102                  05/01/2007
1052                    500                  05/02/2007
1052                    500                  05/01/2007
1052                    500                  04/27/2007
1063                    550                  04/11/2007
1063                    600                  05/02/2007
1063                    600                  05/01/2007

For each unique ENTITY ID, I need to pull just the enc # (if there is
more than 1 occurance of ENC #) with the most recent (or greatest
date). If there is only 1 occurance of the enc #, I need to pull that
as well.

In this instance I would want to pull the following rows:

1044                    101                  05/02/2007
1044                    102                  05/01/2007
1052                    500                  05/02/2007
1063                    550                  04/11/2007
1063                    600                  05/02/2007

Thanks of any help.