Custom ordering??

uraknai · #1 (**permalink**) 09-03-07, 03:16

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

gvee · #2 (**permalink**) 09-03-07, 03:43

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

healdem · #3 (**permalink**) 09-03-07, 03:45

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.

gvee · #4 (**permalink**) 09-03-07, 03:47

A product type table..?

r937 · #5 (**permalink**) 09-03-07, 05:16

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')

gvee · #6 (**permalink**) 09-03-07, 05:27

Ooh I did not know that!
mySQL proprietary?

r937 · #7 (**permalink**) 09-03-07, 05:35

Quote:

Originally Posted by georgev

mySQL proprietary?

yeppir, but useful, no?

gvee · #8 (**permalink**) 09-03-07, 05:51

Yep, no doubting that...
But it's still technically hard-coding the solution!

r937 · #9 (**permalink**) 09-03-07, 06:29

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

uraknai · #10 (**permalink**) 09-03-07, 07:09

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

Internet Services

Web Development

Digital Marketing

Consumer Tech