Hello All: Ashish wants latest output_date but less than now()
>want to retrieve all the output_id which are having latest >output_date but less than now() (no future output_dates) The query written by Tirnath will return count of all output ids given parent id where output_date is less than now, it won't return rows for latest output_date. I propose the following solution: SELECT o.output_id FROM my_table o, parent_table p WHERE o.parent_id = p.parent_id AND o.output_date < now() AND NOT EXISTS ( SELECT out.output_date FROM my_table out WHERE out.parent_id = o.parent_id AND out.output_date < now () AND o.output_date < out.output_date ); The idea is get first maximum date which is less than now(). Assuming now() as the maximum date, the problem reduces to finding second maximum date. The nested query means: the date in context (outer query date) shouldn't be less than any date which is less than now(). This will give you the latest output date grouped by parent_id albeit there's no group clause used. :-) Let me know if there's any mistake. Cheers, Ranbeer Makin On Dec 13, 2007 6:32 PM, Trinath Somanchi <[EMAIL PROTECTED]> wrote: > Hi , > > Try this out > > SELECT count(o.id) > FROM output_table o , parent_table p > WHERE o.pid=p.pid AND o_date < now() > GROUP BY p.pid ; > > > On Thu, 13 Dec 2007 10:00:56 +0000 (GMT) > Ashish Karalkar <[EMAIL PROTECTED]> wrote: > > > Hello List member, > > I have a table which ha sfollowing structure > > > > my_table: > > ( > > output_id serial priimary key, > > parent_id int, > > output_date timesatmp > > ) > > > > parent_table: > > > > (parent_id int, > > parent desc > > ) > > > > the my_table contains data for previous as well as future output > > dates. for single parent there may be multiple rows. > > > > I want to retrieve all the output_id which are having latest > > output_date but less than now() (no future output_dates) group by > > parent_id > > > > Thanks in advance > > > > With Regards > > Ashish... > > > > > > --------------------------------- > > Forgot the famous last words? Access your message archive online. > > Click here. > > -- > Trinath Somanchi , > ([EMAIL PROTECTED]), > > > ******************************************************************************** > This email message (including any attachments) is for the sole use of the > intended recipient(s) > and may contain confidential, proprietary and privileged information. Any > unauthorized review, > use, disclosure or distribution is prohibited. If you are not the intended > recipient, > please immediately notify the sender by reply email and destroy all copies > of the original message. > Thank you. > > Intoto Inc. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
