select ItemGroupDefinitionID,Description from
(
Select 'ALL' as ItemGroupDefinitionID,'DISPLAY ALL PRODUCTS' as Description

union

SELECT cast(A.ItemGroupDefinitionID as varchar),Description FROM [ItemGroupDefinition] A

)B

ORDER BY  CASE WHEN ItemGroupDefinitionID='ALL' THEN 0 ELSE ItemGroupDefinitionID END

I want the result set to be sorted according to the Description  rather than Ids. The condition is such that the first row remains unchanged because I need to display all the products , the sort has to be done from the second row based on the description. 

 result set:

ItemGroupDefinitionID Description
ALL    DISPLAY ALL PRODUCTS
2       Network Integration
3       Network Drops
5      CH Square Feet
6      FH Square Feet
8      EA CH Square Feet
9      EA CH LBand Square Feet

select ItemGroupDefinitionID,Description from
(
Select 'ALL' as ItemGroupDefinitionID,'DISPLAY ALL PRODUCTS' as Description,0 as SortId

union

SELECT cast(A.ItemGroupDefinitionID as varchar),Description FROM [ItemGroupDefinition] A, 1 as SortId

)B

ORDER BY SortId ASC,Description ASC

Hey Sandeep,

Many Thanks !

It worked

Ajit