Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to order by certain rows in SQL

Posted on 2011-10-24
10
Medium Priority
?
456 Views
Last Modified: 2012-05-12
How do I order it so it shows

Regular
Mild
Hot
Spicy

With Spicyness as(
Select 
case 
	when spice_count < 10 then 'Regular'
	when spice_count between 10 and 50 then 'Mild'
	when spice_count between 51 and 70 then 'Hot'
	when spice_count between > 70 then 'Spicy'
end as SpiceFactor,
	spicy_type
From spices 
)
Select SpiceFacor as "How Spicy"
, COUNT(spicy_type) as "Count"
from Spicyness
group by SpiceFactor

Open in new window

0
Comment
Question by:Jintonix415
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 11

Expert Comment

by:maeltar
ID: 37022231
Add :

order by Regular, Mild, Hot, Spicy (ASC or DESC as required)
0
 
LVL 15

Expert Comment

by:Eyal
ID: 37022261
With Spicyness as(
Select 
case 
	when spice_count < 10 then 'Regular'
	when spice_count between 10 and 50 then 'Mild'
	when spice_count between 51 and 70 then 'Hot'
	when spice_count between > 70 then 'Spicy'
end as SpiceFactor,
	spicy_type
From spices 
)
Select SpiceFacor as "How Spicy"
, COUNT(spicy_type) as "Count"
from Spicyness
group by SpiceFactor
order by spice_count

Open in new window

0
 

Author Comment

by:Jintonix415
ID: 37022264
That does not work. I think it is because those values are used as syntax characters instead of an actual field. .
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 11

Expert Comment

by:maeltar
ID: 37022279
With Spicyness as(
Select 
case 
	when spice_count < 10 then 'Regular'
	when spice_count between 10 and 50 then 'Mild'
	when spice_count between 51 and 70 then 'Hot'
	when spice_count between > 70 then 'Spicy'
end as SpiceFactor,
	spicy_type
From spices 
)
Select SpiceFacor as "How Spicy"
, COUNT(spicy_type) as "Count"
from Spicyness
group by SpiceFactor
order by spicy_type

Open in new window

0
 

Author Comment

by:Jintonix415
ID: 37022300
Eyal, that does not work either as I get the aggregate error. I even added it to the group by clause but then it returns all rows.
0
 

Author Comment

by:Jintonix415
ID: 37022316
Maeltar, the spicy_type is used as an ID and as a count. The results show all rows as well.
0
 
LVL 15

Expert Comment

by:Eyal
ID: 37022400
I hope I got it this time :)

With Spicyness as(
Select 
case 
	when spice_count < 10 then 1
	when spice_count between 10 and 50 then 2
	when spice_count between 51 and 70 then 3
	when spice_count between > 70 then 4
end as SpiceFactor,
	spicy_type
From spices 
)
Select case 
	when SpiceFacor = 1 then 'Regular'
	when SpiceFacor = 2 then 'Mild'
	when SpiceFacor = 3 then 'Hot'
	when SpiceFacor = 4 then 'Spicy'
end as "How Spicy"
, COUNT(spicy_type) as "Count"
from Spicyness
group by SpiceFactor
order by SpiceFacor

Open in new window

0
 
LVL 15

Accepted Solution

by:
Eyal earned 1600 total points
ID: 37022405
sorry.. copy problem

With Spicyness as(
Select 
case 
	when spice_count < 10 then 1
	when spice_count between 10 and 50 then 2
	when spice_count between 51 and 70 then 3
	when spice_count between > 70 then 4
end as SpiceFactor,
	spicy_type
From spices 
)
Select case 
	when SpiceFactor= 1 then 'Regular'
	when SpiceFactor= 2 then 'Mild'
	when SpiceFactor= 3 then 'Hot'
	when SpiceFactor= 4 then 'Spicy'
end as "How Spicy"
, COUNT(spicy_type) as "Count"
from Spicyness
group by SpiceFactor
order by SpiceFactor

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 37023231
You can also append the display order on SpiceFactor in your case statement and remove it on the final SELECT clause.
With Spicyness as(
Select 
case 
	when spice_count < 10 then '1Regular'
	when spice_count between 10 and 50 then '2Mild'
	when spice_count between 51 and 70 then '3Hot'
	when spice_count > 70 then '4Spicy'
end as SpiceFactor,
	spicy_type
From spices 
)
Select SUBSTRING(SpiceFactor,2,100) as "How Spicy"
, COUNT(spicy_type) as "Count"
from Spicyness
group by SpiceFactor
ORDER BY SpiceFactor

Open in new window

0
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 400 total points
ID: 37023239
On second thought, you could simply add spice_count on the select statement of the CTE and order by MAX(spice_count)
With Spicyness as(
Select 
case 
	when spice_count < 10 then 'Regular'
	when spice_count between 10 and 50 then 'Mild'
	when spice_count between 51 and 70 then 'Hot'
	when spice_count > 70 then 'Spicy'
end as SpiceFactor,
	spicy_type,
	spice_count
From spices 
)
Select SpiceFactor as "How Spicy"
, COUNT(spicy_type) as "Count"
from Spicyness
group by SpiceFactor
order by MAX(spice_count)

Open in new window

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 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