Solved

Using CASE statement in ORDER BY clause Sql 2005

Posted on 2010-08-18
5
477 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 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