Special order clause

Question

Special order clause

halifaxdal

SSCoach

Points: 19741
More actions
August 20, 2008 at 11:57 am

#75295

Hello,
I have a simple question, is it possible to make order query like:
select ....
order by item ("item1", "item2", "item3")
normally order by clause will be a field, and either asc or desc, but I just wonder if I can use neither asc nor desc, but actually in a special order I want
for example, ("item1", "item2", "item3") is asc
("item3", "item2", "item1") is desc
how about ("item2", "item1", "item3")?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Nope, there is only Order By [column_name(s)] asc/desc.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

halifaxdal SSCoach Points: 19741 More actions · Answer 2

I got it, here is the code:

ORDER BY

Case When NextReviewDate < getDate() then '1'

When NextReviewDate >= getDate() And NextReviewDate < getDate() + 30 then '2'

When NextReviewDate >= getDate() + 30 And NextReviewDate < getDate() + 60 then '3'

When NextReviewDate >= getDate() + 30 And NextReviewDate < getDate() + 90 then '4'

Else '5'

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Yes you can use a CASE expression. Also if you already have the CASE in your Select you can just reference the alias for the CASE.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sergiy SSC Guru Points: 110208 More actions · Answer 4

halifaxdal (8/20/2008)
I got it, here is the code:
ORDER BY
Case When NextReviewDate < getDate() then '1'
When NextReviewDate >= getDate() And NextReviewDate < getDate() + 30 then '2'
When NextReviewDate >= getDate() + 30 And NextReviewDate < getDate() + 60 then '3'
When NextReviewDate >= getDate() + 30 And NextReviewDate < getDate() + 90 then '4'
Else '5'

I believe simple

ORDER BY NextReviewDate

would be as good.

_____________
Code for TallyGenerator