Обсуждение: SQL Query
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.
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.
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
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:
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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend