SELECT with greatest date

[Bart B]

Here is my SQL statement

SELECT a.name, b.meterno, c.meterstatus, d.accountno, d.creation_date
FROM fmdata.location a, cisdata.service_meters b, cisdata.meter_master c,
cisdata.Account_Master d
WHERE a.location_id = b.location_id AND b.meterno IS NOT NULL AND b.meterno
= c.meterno AND a.location_id = d.location_id
ORDER by meterstatus, name

Here is my results

NAME METERNO METERSTATUS ACCOUNTNO CREATION_DATE
01010004 20512944 Active 101000402 7-Feb-2002
01010004 20512944 Active 101000401 8-May-1997
01010005 25917180 Active 101000501 27-May-2003
01011001 13646231 Active 101100102 17-Mar-1999
01011002 18389246 Active 101100201 29-Apr-1994
01011003 84473845 Active 101100301 24-Apr-1997
01012002 47511850 Active 101200202 26-Jan-1996
01013001 35653963 Active 101300101 28-Feb-1979

If you notice I'm getting two 01010004 under the NAME column. I would like
the SQL statement to select the greatest CREATION_DATE if there are
duplicate NAME'S. So in this case, it would select the top 01010004 since it
has the greatest CREATION_DATE.

thanks in advance
bart

 

[kingston via AccessMonster.com]

Make a totals query first where the data is grouped by NAME and returns the
Max of CREATION_DATE. Then link that query to this one using those two
fields.