RE: [GENERAL] SQL Help

> 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