sql server - Order by clause doesn't work when Select DISTINCT from #temp table -

basically, have complicated join statement temporary store result in #temp table, need reuse #temp table multiple other queries. here sample code:

insert #temp_table select --some complicated join statement --many tables --some complicated conditions order case when @orderby = 'lname' (last_name + first_name) end, case when @orderby = 'fname' (first_name + last_name) end 

@orderby accept either 'lname' or 'fname' , cannot null.

until part, working fine.

then, when perform following query:

select distinct * #temp_table 

there absolutely no sorting in result table anymore.

i have tried sorting @ #temp_table, such this:

select distinct * #temp_table order case when @orderby = 'lname' (last_name + first_name) end, case when @orderby = 'fname' (first_name + last_name) end 

but give me error saying "order items must appear in select list if select distinct specified."

notes:

1. there last_name , first_name columns in #temp_table
2. due requirement, cannot combine (last_name + first_name) in select statement. result must show 2 separate columns last_name , first_name
3. preferably, want sorting @ insert statement, because not wish rewrite order by clause in every of select distinct * #temp_table statement.

1. you have specify fields want select
2. as error says, ordered items must appear in select clause.

that done, query should work:

select distinct /*add here field want display*/,     first_name,     last_name,     case when @orderby = 'lname' (last_name + first_name) end last,     case when @orderby = 'fname' (first_name + last_name) end first #temp_table order last, first 

as write output table, no need order before inserting, order lost, noticed.