declare @table table (name varchar(10))
insert into @table values ('Smith'),
('Jack'),
('David'),
('Ellen'),
('john'),
('Peter')

select * from @table order by name asc
select * from @table order by name desc
select *, case name when 'Smith' then 1 when 'jack' then 2 when 'david' then 3 when 'ellen' then 4 when 'john' then 5 when 'peter' then 6 end as sortorder from @table order by sortorder

SELECT name FROM SomeTable ORDER BY sortorder

We walk in the dark places no others will enter
We stand on the bridge and no one may pass