sql - Incredible select

Question

Good day! Need help selecting. I can hardly imagine how to implement it. There is a table clients (client_id, name) There is a table contact (client_id, phone, phone_type (1,2,3,4,5,12), contact_flg (1,0), date_add (timestamp)) A task: For all client_id, find only one phone value with the following priority:

1. Phones, where contact_flg = 1 with the earliest date of addition
2. If the client does not have contact_flg = 1, then the phones with contact_flg = 0 and phone_type = 5 with the earliest date of adding
3. If the client does not have phones from p. 1-2, then phones with contact_flg = 0 and phone_type = 2 with the earliest date of adding
4. If the client does not have phones from p. 1-3, then the phones with contact_flg = 0 and phone_type = any value with the earliest date of addition.
5. If there are no phones for the client at all - output null The first point can be done using row num and sorting, I understand that, but how can the other four fit?

question from:https://stackoverflow.com/questions/65936955/incredible-select

Answer 1

Waitting for answers