Post reply

Order by with value

  • December 14, 2005 at 8:00 am

    #90098

    Hello,

    I have a table with one column named "Letter". The values are "A", "B", "C", "D" (4 rows)

    If i do an Order by Letter i have the same result.

    Now immagine i want to fixed an order like B,A,D,C or C D A B how can i do that ?

    May be something like Order by Letter ('B','A','D','C')

    Thanks for answers

  • December 14, 2005 at 8:46 am

    #609490

    I solve sort issues like this by adding another column like 'LetterSort', don't know if that's an option for you.

    Cheers..

  • December 14, 2005 at 1:53 pm

    #609575

    Thanks, it can be a good solution.

     

     

  • December 15, 2005 at 5:30 am

    #609697

    You can also put all the possible 'Letter' values in their own table - columns would be PrimaryKey, LetterValue, and SortOrder.

    This lets you impose an arbitrary sort order from anywhere in the application. If you always reference the LetterValue by using it's primaryKey value as a foreign key in the table where you want to use it, you can also make a global change to a letter value (or similar) by just changing it in the table.

  • December 15, 2005 at 8:06 am

    #609743

    You can use this: 

    ORDER

    BY CASE Letter

            WHEN 'B' THEN 1

            WHEN 'A' THEN 2

            WHEN 'D' THEN 3

            WHEN 'C' THEN 4 END

    But only when there are few rows, and the ordering is only used in a few places.  If the number of rows gets out of hand, or if the ordering has to be used in many places (some people might say more than once), go with the LetterSort column.

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

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