Hi:

Helen stated in response "You will have to do it in a stored
procedure..."

I have the following query that I want to return many rows but
it only returns one(??):

Any suggestions appreciated.
Best
regards

The data consists of addresses and a maxdate:

TENANTS
ADRESS_TEN STNAME_TEN CITY_TEN STATE_TEN MAXDATE
123 4th st AnyCity MI 12/15/1998
123 4th st AnyCity MI 12/30/1999
123 4th st AnyCity MI 12/15/2000 <----

125 4th st AnyCity MI 12/15/1998
125 4th st AnyCity MI 12/30/1999
125 4th st AnyCity MI 12/15/2000 <----

I want to return one record (<---) for each unique
address/street/city/state and the maxdate (in a five year window)
for that address

SELECT DISTINCT ID, MGRID, CAST(CAST(ADRESS_TEN AS INTEGER) AS
CHAR(7)) AS ADRESS_TEN, STNAME_TEN, CITY_TEN, STATE_TEN, ZIP_TEN,
MAXDATE
FROM TENANTS
WHERE ADRESS_TEN >0 AND MAXDATE >= CURRENT_DATE - 1827 AND
MAXDATE = (SELECT MAX(T.MAXDATE) FROM TENANTS T
WHERE T.ADRESS_TEN = ADRESS_TEN AND
T.STNAME_TEN = STNAME_TEN AND
T.CITY_TEN = CITY_TEN AND
T.STATE_TEN = STATE_TEN)
Order By STATE_TEN Asc, CITY_TEN asc, STNAME_TEN Asc, ADRESS_TEN
Asc , MAXDATE ASC