There are 2 tables:

table_a:

| id | name |

table_b:

| id | table_a_id | name | order |

in the end I want to sort by table_b.order. If B1 -> then get A3, A2, A1

http://sqlfiddle.com/#!15/b1860/2

with t_b as ( select name, table_a_id, "order" from table_b where name = 'B1' ) select a.name, b.name, b."order" from t_b b join table_a a on a.id = b.table_a_id order by b."order" desc;

How can I get rid of with?

http://sqlfiddle.com/#!15/b1860/8

 select a.name, b.name, b."order" from table_a a join table_b b on b.table_a_id = a.id order by case when b.name = 'B1' then b."order" else -1 end desc, a.id asc

but it turns out duplicate records

    1 answer 1

    If I correctly understood what was required, then

     select a.name, b.name, b."order" from table_a a join table_b b on b.table_a_id = a.id and b.name = 'B1' order by b."order" desc;