Select Distinct A+','+B ...Order By Case @Orderby when 1 A+','+B End 1

[PWise]

I am trying to write a SP Select Statement to select distinct rows that contains concatenated fields and to sort depending on a parameter value

Select Distinct A +','+ B As Field1 ,C + ',' + B As Field2,.....
From TableName
inner join ......
Order by 
Case @OrderBy when 1 then A +','+ B end,
Case @OrderBy when 1 then C +','+ D end

This is the error that I get

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Any workaround??

 

[SQLSister]

try putting the select distinct into a derived table, then do the order by outside it. Example

select * from 
(select distinct a+b as field1, c+d as Field2
from table1) myderivedtable
order by field1

"NOTHING is more important in a database than integrity." ESquared

 

[PWise]

Thank You That Worked! Have A star