SQL rank function
SQL rank function

SQL rank function

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.