Re: SQL Query

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 <trinaths(at)intoto(dot)com> 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 <ashish_postgre(at)yahoo(dot)co(dot)in> 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 ,
> (trinaths(at)intoto(dot)com),
>
>
> ********************************************************************************
> 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
>