You get yourself into a situation like: you have to delete a certain row from each group of rows, but it is not by a maximum value and not by a minimum value, but more like second to last.
Your table would look like this, and you would like to delete the second to last row, ordered by date, from each department id.
| Client_id | Department_id | Arrival_date |
|
100 |
10 |
01.01.2013 |
|
101 |
10 |
02.01.2013 |
|
102 |
20 |
03.01.2013 |
|
103 |
20 |
04.01.2013 |
|
104 |
20 |
05.01.2013 |
|
105 |
30 |
06.01.2013 |
|
106 |
30 |
07.01.2013 |
|
107 |
30 |
08.01.2013 |
|
108 |
40 |
09.01.2013 |
|
109 |
40 |
10.01.2013 |
|
110 |
50 |
11.01.2013 |
|
111 |
50 |
12.01.2013 |
|
112 |
50 |
13.01.2013 |
Second to last row from every department id would be the client ids: 100,103,106,108,110. Of course, you can bypass the entire script creation and delete those specific rows, but that is not the point of this exercise. First you need to understand the rank function. Rank() (Partition by Department_id order by Arrival_Date desc) Rank is a keyword for this function. Partition by the field that you want to group. Order by the field that you want to order, ascending or descending.
Now we create a select that ranks each row in the order that you want.
select client_id, department_id, rank() ( partition by department_id order by arrival_date desc) as rank from client_table
You have a clear selection for your rank and the rank you need right now is the second to last, rank 2 from every department.
I will finalize the script for you in a fancy way, so you can actually delete these rows. With deleted_ranks as (select client_id, department_id, rank() ( partition by department_id order by arrival_date desc) as rank from client_table ) Delete from client_table where client_id in (select client_id from deleted_ranks where rank=2)
Enjoy the moment.
No Comments