If you have some way of telling SQL the sort order then yes

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