Home » SQL & PL/SQL » SQL & PL/SQL » Retain first and last record according to id. (oracle 10g)
| Retain first and last record according to id. [message #614396] |
Wed, 21 May 2014 05:33  |
 |
gauravgautam135
Messages: 33
Registered: December 2013
|
Member |
|
|
Hi,
Please see below test data for this question:
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (1, TO_DATE ('05/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (1, TO_DATE ('05/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (1, TO_DATE ('05/19/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (1, TO_DATE ('05/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (1, TO_DATE ('05/24/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (2, TO_DATE ('05/24/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (2, TO_DATE ('05/22/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (2, TO_DATE ('05/21/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (2, TO_DATE ('05/19/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (3, TO_DATE ('05/19/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (4, TO_DATE ('05/19/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (4, TO_DATE ('05/12/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (4, TO_DATE ('05/11/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (4, TO_DATE ('05/29/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (4, TO_DATE ('05/22/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MY_TABLE (ID, ID_DATE)
VALUES (4, TO_DATE ('05/25/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
Now my main focus is around column 'ID'.
For each ID, I want to retain first and last record based on the ID_DATE in descending order.
e.g.
SQL> SELECT id, id_date
2 FROM my_table
3 WHERE id = 1
4 ORDER BY id, id_date DESC;
ID ID_DATE
---------- ---------
1 24-MAY-14
1 20-MAY-14
1 19-MAY-14
1 15-MAY-14
1 10-MAY-14
SQL>
So here i want to retain records with date 24-May-14 & 10-May-14. Rest will be deleted.
Similary we need to do with other ID's present.
Important Point:
If any ID is having only one or two records then those will be not be deleted.
Please let me know if you need anything else.
|
|
|
|
| Re: Retain first and last record according to id. [message #614400 is a reply to message #614396] |
Wed, 21 May 2014 06:24  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Basically FIRST and LAST analytic functions are what you're looking for. But what should be the output if there are several rows having same date for the same ID? with only two columns ID and I_DATE in your table I don't see any other criterion to remove duplicates while sorting by the dense_rank inside.
Also, you could have used simply a date literal (given that the time portion is not apparently relevant in your inserts) instead of to_date. Therefore, for example DATE '2014-01-10' instead of to_date( . . . )
[Updated on: Wed, 21 May 2014 06:26] Report message to a moderator |
|
|
|
| Re: Retain first and last record according to id. [message #614401 is a reply to message #614396] |
Wed, 21 May 2014 06:25 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
"select id, min(id_date) min_dt, max(id_date) max_dt from my_table group by id" gives you the first and last dates.
So you can start from it to delete all but these ones.
Another way is to use ranking analytic function:
delete my_table
where (id, id_date) not in
(select id, id_date
from (select id, id_date,
rank() over (partition by id order by id_date) rk1,
rank() over (partition by id order by id_date desc) rk2
from my_table)
where rk1 = 1 or rk2 = 1)
/
Forgot to mention: thanks for the test case but it should also contain the CREATE TABLE statement.
[Updated on: Wed, 21 May 2014 06:26] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 13:28:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
