Thanks for the question, Shyla.
Asked: October 31, 2015 - 2:05 am UTC
Last updated: November 02, 2015 - 1:41 am UTC
Version: Oracle8i
Viewed 10K+ times! This question is 
You Asked
I have a table Department_history and here is the count of records for each department.
Department_history has the lot of columns and here i am selecting only the department name and finding the no of rows with the same department_name. (My table has multiple rows with the same department_name with the different report_runtime values.
select department_name, id, count(1) total_rows_COUNT from Department_history
where
group by Department_history,id
order by 2 desc
result:
department_name ID total_rows_COUNT
Accounting 4564 556
Finance 3434 671
Marketing 4353 234
IT 1233 454
I want to keep only the 10 records for each department in the table.
if run this below query for each department like this, it works. it keep only the latest 10 records of the the department_name = "accounting" and delete the old ones.
delete from Department_history a1
where
and a1.report_runtime NOT IN
(
select report_runtime
from (
select a.*, rank() over ( partition by department_name, id order by report_runtime desc) r
from Department_history a
) rs
where r <= 10 and department_name = 'Accounting'
)
and department_name = 'Accounting'
But I don't want to individually run this deletion for each department. how can I run a single query that deletes the data for each department_name (if it is >10 records). I tried this. but it doesn't work.
delete from Department_history a1
where
and a1.report_runtime NOT IN
(
select report_runtime
from (
select a.*, rank() over ( partition by department_name, id order by report_runtime desc) r
from Department_history a
) rs
where r <= 10
)
Can someone please advise?
and Connor said...
Here's an example
SQL> drop table T purge;
Table dropped.
SQL> create table T
2 ( dept int,
3 idx int );
Table created.
SQL>
SQL> insert /*+ append */ into T
2 select mod(rownum,10), rownum
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> delete from T
2 where rowid not in
3 (
4 select rid
5 from
6 (
7 select t.rowid rid, rank() over ( partition by dept order by idx desc) r
8 from t
9 )
10 where r <= 10
11 );
99900 rows deleted.
SQL>
SQL> select * from t order by 1,2;
DEPT IDX
---------- ----------
0 99910
0 99920
0 99930
0 99940
0 99950
0 99960
0 99970
0 99980
0 99990
0 100000
1 99901
1 99911
1 99921
1 99931
1 99941
1 99951
1 99961
1 99971
1 99981
1 99991
2 99902
2 99912
2 99922
2 99932
2 99942
2 99952
2 99962
2 99972
2 99982
2 99992
3 99903
3 99913
3 99923
3 99933
3 99943
3 99953
3 99963
3 99973
3 99983
3 99993
4 99904
4 99914
4 99924
4 99934
4 99944
4 99954
4 99964
4 99974
4 99984
4 99994
5 99905
5 99915
5 99925
5 99935
5 99945
5 99955
5 99965
5 99975
5 99985
5 99995
6 99906
6 99916
6 99926
6 99936
6 99946
6 99956
6 99966
6 99976
6 99986
6 99996
7 99907
7 99917
7 99927
7 99937
7 99947
7 99957
7 99967
7 99977
7 99987
7 99997
8 99908
8 99918
8 99928
8 99938
8 99948
8 99958
8 99968
8 99978
8 99988
8 99998
9 99909
9 99919
9 99929
9 99939
9 99949
9 99959
DEPT IDX
---------- ----------
9 99969
9 99979
9 99989
9 99999
100 rows selected.
Now I used rowid above so I could demonstrate perhaps a more efficient method below. Rather than delete all those NOT in the top 10, I could also copy just the top 10 somewhere else, truncate the table, and then re-insert them.
SQL>
SQL> drop table T1 purge;
Table dropped.
SQL> create table T1 as select * from T where 1=0;
Table created.
SQL>
SQL> insert into T1
2 select * from T
3 where rowid in
4 (
5 select rid
6 from
7 (
8 select t.rowid rid, rank() over ( partition by dept order by idx desc) r
9 from t
10 )
11 where r <= 10
12 );
100 rows created.
SQL>
SQL> truncate table T;
Table truncated.
SQL>
SQL> insert into T
2 select * from t1;
100 rows created.
SQL>
SQL> select * from t order by 1,2;
DEPT IDX
---------- ----------
0 99910
0 99920
0 99930
0 99940
0 99950
0 99960
0 99970
0 99980
0 99990
0 100000
1 99901
1 99911
1 99921
1 99931
1 99941
1 99951
1 99961
1 99971
1 99981
1 99991
2 99902
2 99912
2 99922
2 99932
2 99942
2 99952
2 99962
2 99972
2 99982
2 99992
3 99903
3 99913
3 99923
3 99933
3 99943
3 99953
3 99963
3 99973
3 99983
3 99993
4 99904
4 99914
4 99924
4 99934
4 99944
4 99954
4 99964
4 99974
4 99984
4 99994
5 99905
5 99915
5 99925
5 99935
5 99945
5 99955
5 99965
5 99975
5 99985
5 99995
6 99906
6 99916
6 99926
6 99936
6 99946
6 99956
6 99966
6 99976
6 99986
6 99996
7 99907
7 99917
7 99927
7 99937
7 99947
7 99957
7 99967
7 99977
7 99987
7 99997
8 99908
8 99918
8 99928
8 99938
8 99948
8 99958
8 99968
8 99978
8 99988
8 99998
9 99909
9 99919
9 99929
9 99939
9 99949
9 99959
DEPT IDX
---------- ----------
9 99969
9 99979
9 99989
9 99999
100 rows selected.
SQL>
SQL>
Hope this helps.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment