Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Using CASE statement in ORDER BY clause Sql 2005

Hi, I have a set of rows that I need to sort them in order by using case statement.
How can this be done?

sample rows

entity, sales, psales,tsales ( columns )
p1,50,0,100
p2,0,30,90
p3,30,60,0
p4,3,1,2

sorting criteria ( way I need to ouput results)
when entity sales > 0 and tsales > 0, then tsales + psales desc
when entity sales = 0, then tsales + psales desc
when entity sales > 0 and tsales = 0, then sales+psales desc

would order by group similar rows ( based on above ) and sort them in 3 different ways ?

Thanks,
0
ivan_belal
Asked:
ivan_belal
  • 2
  • 2
1 Solution
 
ivan_belalAuthor Commented:
I been to that link. It shows dynamic order by. My case is not. Can you / someone test out my example and provide me with a few snippets would be helpful, thahks.
0
 
BrandonGalderisiCommented:
Try:

select case
  when sales > 0 and tsales = 0 then sales + psales
  when sales > 0 then tsales+psales
  when sales = 0 then stales + psales
end as SortColumn, entity,sales,psales,tsales
from YOURTABLE
order by 1 desc
0
 
vdr1620Commented:
Try that
CREATE TABLE #T
(
Entity Varchar(100),
Sales int,
psales int,
tsales int
)

INSERT INTO #T VALUES ('p1',50,0,100)
INSERT INTO #T VALUES ('p2',0,30,90)
INSERT INTO #T VALUES ('p3',30,60,0)
INSERT INTO #T VALUES ('p4',3,1,2)

SELECT * FROM #T
ORDER BY CASE WHEN  sales > 0 and tsales > 0 then (tsales + psales ) 
when sales = 0 then tsales + psales 
when sales > 0 and tsales = 0 then sales+psales END Desc

Open in new window

0
 
ivan_belalAuthor Commented:
Looks like BrandonGalderisi's solution I needed. I will get back after a few tests, thanks...
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now