RE: [GENERAL] SQL Help
От | Jackson, DeJuan |
---|---|
Тема | RE: [GENERAL] SQL Help |
Дата | |
Msg-id | F10BB1FAF801D111829B0060971D839F45B71A@cpsmail обсуждение исходный текст |
Список | pgsql-general |
> 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
В списке pgsql-general по дате отправления: