How to order by rows according to multiple columns

I have this table list of records to be sorted.

where MAIN_NAME is the father’s name, STUDENT_NAME is their son/daughter’s name. Null value if the record is the father itself, and IDENTIFIER wherein 1 represents data is of father entity while 2 is student.

I’ve tried ordering them by CREATED_DATE, MAIN_NAME. But all records with identifier 1 will be on top of all the records with identifier 2.

Have also tried to MAIN_NAME, CREATED_DATE. But MAIN_NAME=ANDREW will be coming in first than Joseph, which registered earlier than the former.

I’m bit confused since the dates are all unique.

Here this should be the expected result.

The records should be ordered by CREATED_DATE but still student should be tagged under their father’s (MAIN_NAME) record.

Answer

You need conditional sorting:

select t.*
from tablename t
order by
  case t.identifier 
    when 1 then t.registration_date
    when 2 then (select min(registration_date) from tablename where main_name = t.main_name)
  end,
  t.main_name,
  t.student_name

See the demo.
Results:

> registration_date   | main_name | student_name | identifier
> :------------------ | :-------- | :----------- | ---------:
> 01/01/2019 00:00:00 | JOSEPH    |              |          1
> 06/08/2019 00:00:00 | JOSEPH    | CHOU         |          2
> 06/08/2019 00:00:00 | JOSEPH    | LOUIS        |          2
> 02/01/2019 00:00:00 | ANDREW    |              |          1
> 03/01/2019 00:00:00 | ANTHONY   |              |          1
> 01/08/2019 00:00:00 | ANTHONY   | CAMILLIA     |          2
> 01/08/2019 00:00:00 | ANTHONY   | PERCY        |          2
> 01/08/2019 00:00:00 | ANTHONY   | SEAN         |          2
> 01/08/2019 00:00:00 | ANTHONY   | TOM          |          2