Need to sort by

All Replies

• Tuesday, February 05, 2013 4:45 PM

   

   
  

  0

  pls try

  SELECT distinct
  	o.[Type],
  	o.[Engine],
  	o.[Usage],
  	o.[UsageRate],
  	o.[MaxTerm]
  	FROM [OpRate]as o , [Types] as t
  	inner join [OpRate] on
  	t.Type = [OpRate].Type
  	
  	where ((o.Type > 499) and (o.Type < 541)) And 
  	      ((t.Type > 499) and (t.Type < 541))
  	and (o.usageRate <> '0.00' or o.UsageRate <> null) 
  order by o.Type, CASE WHEN 
  o.Usage = 'L' THEN 1 WHEN o.Usage='M' THEN 2 ELSE 3 end

  vt
  

  ---

  Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

  • Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 05, 2013 4:48 PM
  • Unproposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 05, 2013 5:14 PM
  •  
  • Reply
  • Quote

   
• Tuesday, February 05, 2013 4:50 PM

   

   
  

  0

  Msg 145, Level 15, State 1, Line 1

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

  ---

  Mathieu Alexandre Cupryk www.omegalove.com

  • Reply
  • Quote

   
• Tuesday, February 05, 2013 4:53 PM

   

   
  

  0

  sorry..

  ;WITH rs
  AS
  (
  SELECT distinct
  	o.[Type],
  	o.[Engine],
  	o.[Usage],
  	o.[UsageRate],
  	o.[MaxTerm]
  	FROM [OpRate]as o , [Types] as t
  	inner join [OpRate] on
  	t.Type = [OpRate].Type
  	
  	where ((o.Type > 499) and (o.Type < 541)) And 
  	      ((t.Type > 499) and (t.Type < 541))
  	and (o.usageRate <> '0.00' or o.UsageRate <> null) 
  	)
  SELECT * FROM rs 
  order by Type, CASE WHEN 
  Usage = 'L' THEN 1 WHEN Usage='M' THEN 2 ELSE 3 end

  
  vt

  ---

  Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

  
  

  • Edited by v.vtMicrosoft Community Contributor Tuesday, February 05, 2013 4:54 PM
  • Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 05, 2013 5:14 PM
  • Marked As Answer by macupryk Tuesday, February 05, 2013 5:55 PM
  •  
  • Reply
  • Quote

   
• Tuesday, February 05, 2013 4:55 PM

   

   
  

  0

  this is tricky.

  ;WITH rs
  AS
  (
  SELECT distinct
  	o.[Type],
  	o.[Engine],
  	o.[Usage],
  	o.[UsageRate],
  	o.[MaxTerm]
  	FROM [OpRate]as o , [Types] as t
  	inner join [OpRate] on
  	t.Type = [OpRate].Type
  	
  	where ((o.Type > 499) and (o.Type < 541)) And 
  	      ((t.Type > 499) and (t.Type < 541))
  	and (o.usageRate <> '0.00' or o.UsageRate <> null) 
  	)
  SELECT * FROM rs 
  order 
  by rs.Type, 
  CASE WHEN 
  rs.Usage = 'L' 
  THEN 1 
  WHEN rs.Usage='M' 
  THEN 2 ELSE 3 end

  
  

  ---

  Mathieu Alexandre Cupryk www.omegalove.com

  • Proposed As Answer by Ahsan Kabir Tuesday, February 05, 2013 5:04 PM
  •  
  • Reply
  • Quote

   
• Tuesday, February 05, 2013 5:03 PM

   

   
  

  0

  May be you can try the below alternate method of using ASCII funtion instead of CASE statement in ORDER BY clause.

  However, you need to include derived table or common table expression because you want DISTINCT records as well as custom ordering of the records.

  ;WITH rs
  AS
  (
  	SELECT	distinct
  			o.[Type],
  			o.[Engine],
  			o.[Usage],
  			o.[UsageRate],
  			o.[MaxTerm]
  	FROM		[OpRate]as o , [Types] as t
  	inner join	[OpRate] 
  	on			t.Type = [OpRate].Type
  	where ((o.Type > 499) and (o.Type < 541)) And 
  	      ((t.Type > 499) and (t.Type < 541))
  	and (o.usageRate <> '0.00' or o.UsageRate <> null) 
  )
  SELECT	* 
  FROM	rs 
  order by rs.Type, ASCII(rs.Usage) % 15

  Thanks!
  • Reply
  • Quote