Return the query in a particular format

• rasikawtc

  Old Hand

  Points: 312

  More actions

  June 22, 2011 at 9:34 pm

  #389820

  Here is my query I need to get All in the first row and the remaining in the alphabetical order

  select distinct State_Code,

  case when State_Code=' '

  then 'Unknown'

  else State_Code

  end State_Code2,

  1 as 'State_Code1'

  from Dim_Prop_Geo_T

  union

  select 'All' as State_Code, 'All' ,0 as 'orderby'

  order by 'State_Code2'

  This query returns in alphabetical order .....I need all in the first row

• June 23, 2011 at 2:38 am

  This was removed by the editor as SPAM

• manub22

  Hall of Fame

  Points: 3133

  More actions

  June 23, 2011 at 3:14 am

  #1342880

  You need CASE with ORDER BY clause, try this:

  with cte as (

  select distinct

  State_Code,

  case when State_Code=' ' then 'Unknown'

  else State_Code

  end State_Code2,

  1 as 'State_Code1'

  from Dim_Prop_Geo_T

  union

  select 'All' as State_Code,

  'All' ,

  0 as 'orderby')

  select State_Code, State_Code2, State_Code1

  from cte

  order by case when State_Code='All' then 0 else 1 end

  ... you may need to tweak the query for correct output.

  For more info on using CASE with ORDER BY clause check this link: http://sqlwithmanoj.wordpress.com/?s=order+by+case

  ---

  ~manoj | LinkedIn | Facebook | YouTube
  Blog: SQLwithManoj.com