Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2006
    Posts
    41

    Unanswered: Custom ordering??

    Hi,

    is there a way to make the results order in a custom way. So, rather than alphabetically or numeriacally you can set the order in which the results appear.

    So, for example, if one column had only the values APPLE, ORANGE, BANANA, PEAR I could say arrange the results in the custom order [ORANGE, BANANA, APPLE, PEAR].

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @fruits table (fruitName varchar(10), sortOrder int)
    
    INSERT INTO @fruits(fruitName) VALUES('Orange')
    INSERT INTO @fruits(fruitName) VALUES('Apple')
    INSERT INTO @fruits(fruitName) VALUES('Pear')
    INSERT INTO @fruits(fruitName) VALUES('Banana')
    
    SELECT fruitName
         , CASE fruitName
             WHEN 'Orange' THEN 1
             WHEN 'Banana' THEN 2
             WHEN 'Apple'  THEN 3
             ELSE 4
           END As 'sortOrder'
    FROM   @fruits
    ORDER
       BY  x ASC
    
    --OR--
    
    UPDATE @fruits SET sortOrder = 1 WHERE fruitname = 'Orange'
    UPDATE @fruits SET sortOrder = 2 WHERE fruitname = 'Banana'
    UPDATE @fruits SET sortOrder = 3 WHERE fruitname = 'Apple'
    UPDATE @fruits SET sortOrder = 4 WHERE fruitname = 'Pear'
    
    SELECT fruitName
         , sortOrder
    FROM   @fruits
    ORDER
       BY  sortOrder ASC
    Personally I'd go for the second option (storing the sort order).
    Hard-coding stuff is rubbish
    Last edited by gvee; 09-03-07 at 04:51. Reason: minor amendments to syntax
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use something like a product type table and defien your sort rules there, the product type can refer to itself to give a tree hierarchy
    eg
    Product Type
    ID autonumber PK
    Desc Text
    ParentID FK to ID in ProductType
    sample data...
    1 Fresh Produce <NULL>
    5 Fruit 1
    3 Vegetables 1
    10 Root Vegetable 3
    11 Leaf Vegetables 3
    9 Potatoes 10
    8 King Edward 9
    .....

    or you can allocate a column say called SortSequence to the product column

    Product
    ID-autonumber PK
    Desc - text
    SortSequence
    eg
    10 APPLE 5
    14 ORANGE 0
    18 BANANA 4
    201 PEAR 99
    then using the sortseq as a key the will come out int he preferred order. You could chose a mix to give product categories and sort sequence, its optional if you make the sort sequence unique (persoanlly Iwouldn't, I'd use sortseq and (say) desc so that if a sort sequence isn't provided they will come out in alpha order.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A product type table..?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by uraknai
    So, for example, if one column had only the values APPLE, ORANGE, BANANA, PEAR I could say arrange the results in the custom order [ORANGE, BANANA, APPLE, PEAR].
    Code:
    ORDER
        BY FIELD(myfruits,'orange','banana','apple','pear')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ooh I did not know that!
    mySQL proprietary?
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    mySQL proprietary?
    yeppir, but useful, no?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, no doubting that...
    But it's still technically hard-coding the solution!
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Yep, no doubting that...
    But it's still technically hard-coding the solution!
    that is true

    but hardcoding is not unfailingly always a bad solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2006
    Posts
    41
    Thanks guys.

    I used the
    ORDER
    BY FIELD(myfruits,'orange','banana','apple','pear')

    method since it was the simplest to integrate into my existing code. In the future I'll probably use georgev's method of making a sort order column.

    Cheers

Posting Permissions

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