Grouping field in alphabetical order not in order needed

  • 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!

  • 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!

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

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

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

  • 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!

  • 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.

  • 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