Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using CASE statement in ORDER BY clause Sql 2005

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS  - Dropdown with Null 3 28
SQL Server Count where two id types exist in column 8 29
Present Absent from working date rage 11 36
TSQL recursive CTE challenge... 8 29
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, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…

856 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