Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

one subquery using order by and limit for delete

Status
Not open for further replies.

RahulGade

Programmer
Jul 17, 2001
25
IN
I have to build one function which will delete all the record in the table which satisfies specified condition except latest 20 records .
for this i have writen one query which seems logically good but not working on postgreSQL, because postgreSQL doesn't support order by and limit inside subquery.
Please tell me any other way to achieve this task, since its very important for me.
Thanks for quick response

My Query is :

create function funNotificationLimit() returns opaque
as
'declare serverId int;
begin
serverId=(select NEW.srvserverid);

delete from reporttable where srvserverid=serverId and
rptreportid not in(select rptreportid from reporttable
where srvserverid=serverId order by rpttimestamp desc limit 20);

return NEW;
end;'
language 'plpgsql';
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top