Solved

Using CASE statement in ORDER BY clause Sql 2005

Posted on 2010-08-18
5
474 Views
Last Modified: 2012-05-10
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
Comment
Question by:ivan_belal
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33466303
0
 

Author Comment

by:ivan_belal
ID: 33466346
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 33466394
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33466437
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
 

Author Comment

by:ivan_belal
ID: 33485108
Looks like BrandonGalderisi's solution I needed. I will get back after a few tests, thanks...
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Joining Two Tables In SQL and combining records 6 52
sql query help 4 44
Restrict result set 1 33
MS SQL Inner Join - Multiple Join Parameters 2 17
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now