Home » SQL & PL/SQL » SQL & PL/SQL » Delete small expiry date against pcode (Oracle,9i,XP)
Delete small expiry date against pcode [message #378964] Sat, 03 January 2009 04:02 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

I have a table, name as lts that contains more than 500000 records,the problem is that against "pcode column " ,table has multiple values in hr column depending upon the expiry data.like this

pcode           model           hr      Expiry date    

23227R00	HF**A		1.1	20080831
23227R00	HE**A		1.3	20080930
23227R00	HF**A		1.5	20070930


now i want to delete all pcode except 2nd record which has expiry date "20080930" means greate than all expiry date against pcode.

my structure of a table is below

pcode varchar2(10)
model varchar2(5)
hr varchar2(5)
expirydate date


pls suggest how can i delete those pcode which has expiry date smaller than all
Re: Delete small expiry date against pcode [message #378965 is a reply to message #378964] Sat, 03 January 2009 04:07 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
sorry for upload my problem twice ,actually there is some problem with my internet
Re: Delete small expiry date against pcode [message #378966 is a reply to message #378964] Sat, 03 January 2009 04:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As it appears that you don't care about other columns, one way might be as follows:
DELETE FROM lts l
WHERE l.expirydate <> (SELECT MAX(l1.expirydate)
                       FROM lts l1
                       WHERE l1.pcode = l.pcode
                      );
Re: Delete small expiry date against pcode [message #378970 is a reply to message #378966] Sat, 03 January 2009 05:23 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thank you very much ,I will try it and let you know.
Re: Delete small expiry date against pcode [message #378983 is a reply to message #378964] Sat, 03 January 2009 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want to delete all pcode except 2nd record which has expiry date "20080930"

Is it always the second or always the one with expiry date "20080930"?

And what does mean:
Quote:
means greate than all expiry date against pcode.


Regards
Michel

[Updated on: Sat, 03 January 2009 09:19]

Report message to a moderator

Re: Delete small expiry date against pcode [message #379038 is a reply to message #378983] Sun, 04 January 2009 02:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I understood, it is not "the second" every time; in this example, the second record has the greatest date). He'd like to remove all records but the one which has the greatest date (grouped by "pcode" column).
Re: Delete small expiry date against pcode [message #379042 is a reply to message #379038] Sun, 04 January 2009 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe but no where it is specified.
The only specification I can see is:
Quote:
delete those pcode which has expiry date smaller than all

Which answer is obviously "do nothing" as no row has a date smaller than all dates.

Regards
Michel
Re: Delete small expiry date against pcode [message #379147 is a reply to message #379042] Mon, 05 January 2009 02:25 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thnaks for the reply specially Littlefoot because I have used your query and find my desired output.Once againg thank you very much
Previous Topic: Trigger (merged)
Next Topic: Number format
Goto Forum:
  


Current Time: Fri Dec 09 13:31:00 CST 2016

Total time taken to generate the page: 0.07964 seconds