SQL Server conditional order by issue

Question

1st set of code

---

drop table if exists #tmp1
CREATE table #tmp1
(
Ticker VARCHAR(10),
BrokerName VARCHAR(100)
)

insert into #tmp1(Ticker,BrokerName)
VALUES('MTH','JP Morgan'),
('MTH','Morgan Stanley'),
('MTH','Singular Research'),
('MTH','Cowen & Company')

select * from #tmp1
ORDER BY CASE WHEN (Ticker='MTH' AND BrokerName = 'Cowen & Company') THEN 1
ELSE 0 END ASC

drop table if exists #tmp1

this above code is working fine. when Ticker is MTH and broker name is Cowen & Company then broker name is coming at bottom which is perfect as per requirement but when ticker name is other than MTH then all broker name should be sorted ascending order

see second set of code where i try to do it but not working

drop table if exists #tmp1
CREATE table #tmp1
(
Ticker VARCHAR(10),
BrokerName VARCHAR(100)
)

insert into #tmp1(Ticker,BrokerName)
VALUES('GOGO','JP Morgan'),
('GOGO','Morgan Stanley'),
('GOGO','Singular Research'),
('GOGO','Cowen & Company')

select * from #tmp1
ORDER BY CASE WHEN (Ticker='MTH' AND BrokerName = 'Cowen & Company') THEN 1 END,
 CASE WHEN Ticker<>'MTH' THEN 0 END ASC

drop table if exists #tmp1

so please guide how could i write a generic sql where specific broker name should appear at bottom when ticker is MTH but if ticker name is not MTH then broker name should be sorted as ascending order.

thanks

Answer 1

Try writing this query:

select * from #tmp1
ORDER BY CASE WHEN Ticker='MTH' AND BrokerName = 'Cowen & Company' THEN 1 END, BrokerName