November 4, 2016 at 12:38 pm
Hi All,
I only have five categories in my table. I don't have any other data in my table:
[Code]
Tabl1
Col1
Office
Transportation
Prod
Share
Sports
[/code]
I need to sort them in the below order
Prod
Share
Sports
Transportation
Office
How can I achieve the above sorting.
November 4, 2016 at 12:41 pm
Use a combination of ORDER BY and CASE.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2016 at 2:46 pm
I tried doing this:
select * from testTable
order by
case TestName
when 'Prod' then 1
when 'Share' then 2
when 'Sports' then 3
when 'Transportation' then 4
when 'office' then 5
end
it doesn't sort the way, I want.
November 4, 2016 at 2:54 pm
anjaliagarwal5 (11/4/2016)
I tried doing this:
select * from testTable
order by
case TestName
when 'Prod' then 1
when 'Share' then 2
when 'Sports' then 3
when 'Transportation' then 4
when 'office' then 5
end
it doesn't sort the way, I want.
If that's not working, try it this way instead:
SELECT *,
CASE TestName
WHEN 'Prod' THEN 1
WHEN 'Share' THEN 2
WHEN 'Sports' THEN 3
WHEN 'Transportation' THEN 4
WHEN 'Office' THEN 5
END AS SORT_FIELD
FROM testTable
ORDER BY SORT_FIELD;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 4, 2016 at 2:58 pm
What you tried should be working. Worked for me.
WITH cte AS (
SELECT 'Prod' AS Col1
UNION ALL
SELECT 'Share' AS Col1
UNION ALL
SELECT 'Sports' AS Col1
UNION ALL
SELECT 'Transportation' AS Col1
UNION ALL
SELECT 'Office' AS Col1
)
SELECT *
FROM cte AS t
ORDER BY
CASE t.Col1
WHEN 'Prod' THEN 1
WHEN 'Share' THEN 2
WHEN 'Sports' THEN 3
WHEN 'Transportation' THEN 4
WHEN 'office' THEN 5
END
Output:
Prod
Share
Sports
Transportation
Office
This matches exactly what you asked for.
November 8, 2016 at 12:40 am
Hi Anjali,
whatever you tried (that is what suggested by phil as well) is the shortest way to achieve this requirement.
Thanks,
Prabhu
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply