SQL with greatest date

[BB]

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

[Anith Sen]

There are different ways you can do this. One option is to use a correlated
subquery as an additional SQL predicate in the WHERE clause like:

....
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
AND d.creation_date = ( SELECT MAX( d2.creation_date )
FROM cisdata.Account_Master d2
WHERE a.location_id = d2.location_id )
ORDER BY ...

--
Anith

[BB]

thanks, that worked great

"Anith Sen" <an***@bizdatasolutions.comwrote in message
news:ei***********@news.ndhu.edu.tw...

There are different ways you can do this. One option is to use a
correlated subquery as an additional SQL predicate in the WHERE clause
like:

...
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
AND d.creation_date = ( SELECT MAX( d2.creation_date )
FROM cisdata.Account_Master d2
WHERE a.location_id = d2.location_id )
ORDER BY ...

--
Anith