Solved

Using CASE statement in ORDER BY clause Sql 2005

Posted on 2010-08-18
5
473 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
Comment Utility
0
 

Author Comment

by:ivan_belal
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Looks like BrandonGalderisi's solution I needed. I will get back after a few tests, thanks...
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help with a query. 10 43
Grid querry results 41 51
Auditing in Azure SQL Database 3 27
Delete Trigger in SQL Server2008R2 5 0
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

11 Experts available now in Live!

Get 1:1 Help Now