From: | "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com> |
---|---|
To: | anil <anil(at)server(dot)awcoldstream(dot)com>, PostgreSQL <pgsql-general(at)postgreSQL(dot)org> |
Subject: | RE: [GENERAL] SQL Help |
Date: | 1998-10-07 16:34:40 |
Message-ID: | F10BB1FAF801D111829B0060971D839F45B71A@cpsmail |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-10-07 17:56:18 | Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF) |
Previous Message | Jackson, DeJuan | 1998-10-07 16:14:04 | RE: [GENERAL] Performance |