create table #Client
(
	ClientID int identity,
	Name char(2)
)

insert #Client
select 'C1' union all
select 'C2' union all
select 'C3'

create table #Orders
(
	OrderID int identity,
	ClientID int,
	OrderName char(2)
)

insert #Orders
select 1, 'O1' union all
select 1, 'O2' union all
select 2, 'O3' union all
select 2, 'O4' union all
select 3, 'O5' union all
select 3, 'O6'

create table #OrderInfo
(
	OrderInfoID int identity,
	OrderID int,
	Priority int
)

insert #OrderInfo
select 1, 7 union all
select 1, 1 union all
select 2, 9 union all
select 2, 3 union all
select 3, 4 union all
select 5, 2 union all
select 7, 5 union all
select 7, 8 union all
select 8, 0


select c.Name
from #OrderInfo oi
join #Orders o on oi.OrderID = o.OrderID
join #Client c on c.ClientID = o.ClientID
group by c.Name
order by max(oi.Priority)

drop table #Client
drop table #Orders
drop table #OrderInfo