Solved

Using CASE statement in ORDER BY clause Sql 2005

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 55
how to install/upgrade the Blitz responder kit 8 42
View SQL 2005 Job package 16 43
MS SQL BCP Extra Lines Between Records 2 18
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 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