April 4, 2013 at 7:23 am
Hello,
I have a field called Priority that I am using in a report. The field has 6 possible entries, Top Priority, High, Medium, Low, Maintenence, and None. The Priority field is used in a table where each record in the table is given a priority. I am grouping the report by the Priorty field. The problem is when I run the report, the Priority grouping is in alphabetical order, not in the order above. Is there a way I can assign a number to each of the entries above (i.e. 1 = Top Priority, 2 = High, etc.), and ten group by the number?
Thank you for your help!
April 4, 2013 at 7:30 am
Sure :
...
ORDER BY
CASE
WHEN 'Top Priority'
THEN 1
WHEN 'High'
THEN 2
WHEN 'Medium'
THEN 3
WHEN 'Low'
THEN 4
WHEN 'Maintenence'
THEN 5
WHEN 'None'
ELSE 6
END
Lowell
April 4, 2013 at 8:00 am
Thank you! I would then add this Case to the expression in the grouping field?
April 4, 2013 at 8:11 am
Thank you! I would then add this Case to the expression in the grouping field?
April 4, 2013 at 8:15 am
You'll notice that Lowell prefaced it with Order By... this case structure goes in the Order By portion of your statement.
April 4, 2013 at 8:41 am
without the real code, i could just post a snippet:
but it something like this:
SELECT ColumnList,Priority
From YourTable
GROUP BY ColumnList,Priority
ORDER BY
CASE Priority --the column we will custom order by
WHEN 'Top Priority'
THEN 1
WHEN 'High'
THEN 2
WHEN 'Medium'
THEN 3
WHEN 'Low'
THEN 4
WHEN 'Maintenence'
THEN 5
WHEN 'None'
ELSE 6
END
Lowell
April 4, 2013 at 9:06 am
Or this:
WITH Priorities as (
SELECT
Priority,
PriorityOrder
FROM
(VALUES ('Top Priority',1),
('High',2),
('Medium',3),
('Low',4),
('Maintenence',5),
('None',6))dt(Priority,PriorityCode)
)
SELECT
yt.ColumnList,
yt.Priority
From
YourTable yt
inner join Priorities p
on (yt.Priority = p.Priority)
GROUP BY
yt.ColumnList,
yt.Priority
ORDER BY
p.PriorityCode;
Or you can put the info in the CTE into an actually table and join to that table. The benefit of the actual table is that you can add. delete, modify the data in the table and not have to modify the code.
April 5, 2013 at 9:52 am
Lynn Pettis (4/4/2013)
Or you can put the info in the CTE into an actually table and join to that table. The benefit of the actual table is that you can add. delete, modify the data in the table and not have to modify the code.
This is the way I would go. The other ways require that you modify the code whenever there would be a new priority added. It is much better to have them stored in a table, rather than take the chance that you miss a piece of code somewhere. It seems likely that this priority list will be used in more than one procedure. The more places this code is found, the more complex the modifications become. It's almost a guarantee that one will be missed, especially if someone else takes over the codebase.
Dana
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply