SQL Query

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@yahoo.co.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@intoto.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.

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@intoto.com> wrote: