By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test(ID int, Parent_ID int, Name varchar(20));
insert into test values
( 1 , NULL,'One dove')
,( 2 , 1,'One play')
,( 3 , NULL,'Monitor')
,( 4 , 1,'Day one')
,( 5 , NULL,'Drone')
,( 6 , NULL,'Screen')
,( 7 , NULL,'Done with you')
,( 8 , NULL,'Not done')
,( 9 , NULL,'All as one')
;
select * from test;
Records: 9 Duplicates: 0 Warnings: 0
| ID | Parent_ID | Name |
|---|---|---|
| 1 | null | One dove |
| 2 | 1 | One play |
| 3 | null | Monitor |
| 4 | 1 | Day one |
| 5 | null | Drone |
| 6 | null | Screen |
| 7 | null | Done with you |
| 8 | null | Not done |
| 9 | null | All as one |
select *
from(
select *
,case when instr(Name,' one ')>0 then 1
when instr(Name,'one ')=1 then 1
when instr(Name,' one')>0 then 1
when instr(Name,'one')>0 then 2
else 0
end priority
from test t
)a
where priority>0
order by priority,id
| ID | Parent_ID | Name | priority |
|---|---|---|---|
| 1 | null | One dove | 1 |
| 2 | 1 | One play | 1 |
| 4 | 1 | Day one | 1 |
| 9 | null | All as one | 1 |
| 5 | null | Drone | 2 |
| 7 | null | Done with you | 2 |
| 8 | null | Not done | 2 |