Grouping field in alphabetical order not in order needed

Question

Post reply

Grouping field in alphabetical order not in order needed

robert693

SSChasing Mays

Points: 623
More actions
April 4, 2013 at 7:23 am

#276163

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!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

robert693 SSChasing Mays Points: 623 More actions · Answer 2

Thank you! I would then add this Case to the expression in the grouping field?

robert693 SSChasing Mays Points: 623 More actions · Answer 3

Thank you! I would then add this Case to the expression in the grouping field?

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

You'll notice that Lowell prefaced it with Order By... this case structure goes in the Order By portion of your statement.

Lowell SSC Guru Points: 323504 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

danaanderson SSC Eights! Points: 858 More actions · Answer 7

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