> Hello!
>
> I have a tabe :
> addates : (adid int 4,rundate date,posted boolean,dateid int4 unique
> create no. from sequence datesequence)
>
> table is like this
> adid
> rundate posted dateid
> 1031 05-17-1998
> t 1856
> 1031 06-20-1998
> t 2032
> 1031 06-23-1998
> t 2056
> 1031 06-30-1998
> f 2077
> 1055 05-21-1986
> t 456
> 1055 01-01-1998
> t 987
> 1055 06-30-1998
> f 2089
> I do have lots of entries like this - 30,000 rows
>
> what I want to find is -adid of records that has posted=f for one
> pertticuler day (ex. 06-30-1998) and last run date (maximum date
> value
> which is mark as true for each and every record of adid field)
>
> should be like this adid rundate lastposted
> date datedid
> 1031 06-30-1998
> 06-23-1998 2077
> 1055 06-30-1998
> 01-01-1998 987
SELECT a1.adid, a1.rundate, a2.rundate AS "lastposted date", a1.dateid
FROM addates a1, addates a2
WHERE a1.rundate = '6-30-1998' AND
a1.posted = false AND
a1.adid = a2.adid AND
a2.rundate = (SELECT MAX(addates.rundate) FROM addates
WHERE addates.adid = a1.adid AND
addates.rundate < a1.rundate AND
addates.posted = true);
> How do I do this??
That should do it, but I won't guarantee it as the best way.
> Thank you very much in advance for any thoughts.
>
> Cheers
>
> Anil
-DEJ
