Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    California
    Posts
    40

    Unanswered: Specific ordering

    Hi, I was wondering, I have several columns of data that are able to be sorted on - the user just clicks on the column name. However, we have 1 column called order status where we don't want the order to be alphabetical (and it is a text field - a couple current possible values are Processing...In Production...Waiting For Approval). This order is not good for logical sorting - we would want to be able to specify what value would get order. Does that make sense? So we would want to be able to say that Processing is first in the display, Waiting For Approval would be next, then In Production would be next, and so on. Is there any possible way of doing this in a SQL query? Or am I going to have to manually modify all the data adding numbers to the beginning of each data so that it will sort on the numbers? Thanks everyone.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...

    order by case [order status] when 'Processing' then 1 when 'Waiting for Approval' then 2 when 'In Production' then 3 else 4 end
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Great! Thank you so much - it works great - that's what I wanted!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That'll do if you can't modify the database schema, but if you have a table of Order Status choices you are better off adding a SortOrder column to it than hard-coding your stored procedures.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I'd even create a table OrderStatus, with OrderStatusID int, OrderStatusDesc varchar(whatever), and of course in Orders table store the OrderStatusID. And when the SELECT...ORDER BY comes, I'd join those 2 tables to retrieve the description and...oh geeeee, the status field also comes handy, doesn't it?!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •