ORDER BY, Sorting Question

[sftl99]

I've got some part #'s that I'm trying to sort, but ORDER BY PartNumber is outputting logically, but logic in this case doesn't help. Is there any way to manipulate the ORDER BY?

My specific case:
128MB, 256MB, 512MB, 1GB, 2GB, 4GB is the order it SHOULD go in, but obviously 1GB is coming before 256MB or 512MB, and other issues as well.

Is there a workaround for this?

[fci]

add a column called order_by and manually set the order .. not sure if there really is any other simple solution.

[sftl99]

I thought about doing the whole thing manually, I guess I could write the code, but how do I set a sort variable to a field that hasn't been pulled from the table yet? Does that question make sense?

oh wait, you mean a column in the table itself? woah, that would take forever!

[fci]

mm, after some googling (testing):

SELECT *
FROM your_table
ORDER BY FIELD( some_column, '128MB', '1GB' )

from the docs:

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0

http://dev.mysql.com/doc/refman/5.0/...functions.html

[Kid Charming]

Using FIELD like that will only force the 128 MB to list before the 1GB -- the other sizes will still be out of order, though. Try:

SELECT
 foo
FROM
 bar
ORDER BY
 CASE RIGHT(size_column,2)
  WHEN 'MB' THEN 0
  WHEN 'GB' THEN 1
  ELSE 2
 END
,size_column

This will force everything that ends in MB to order before those ending in GB. Note that if you use G instead of GB, this won't work, of course.

[sftl99]

ORDER BY FIELD( Description1, '128MB', '256MB', '512MB', '1GB' )

that works, only there's a lot more variables involved that i just didn't mention like 128MB DIMM, 512MB ECC DIMM, 1GB Single Rank, 2GB Dual Rank, and so on and so forth. I guess I'll have to go to my database and figure out every single variable and sort it the way I want. Ugh!

EDIT:
Just saw the CASE RIGHT thing, but same problem...the actual description isn't exactly what I put up top.

[fci]

Originally Posted by Kid Charming

Using FIELD like that will only force the 128 MB to list before the 1GB -- the other sizes will still be out of order, though.

then:

SELECT *
FROM test
ORDER BY FIELD( a, '128MB', '1GB', a)

Try:

SELECT
 foo
FROM
 bar
ORDER BY
 CASE RIGHT(size_column,2)
  WHEN 'MB' THEN 0
  WHEN 'GB' THEN 1
  ELSE 2
 END
,size_column

This will force everything that ends in MB to order before those ending in GB. Note that if you use G instead of GB, this won't work, of course.

I like the use of the case, I don't use as much of the db wizardy.. all versions of mysql support it?

[Kid Charming]

Yeah, I posted too fast. You can set up a FIELD to handle the rest of the options.

CASE is standard SQL (unlike IF), and it works across all MySQL versions.

If you've got more than size in your column, though, you've got design issues you should fix instead of trying to work around them in your query. Do a Google on 'database normalization' for more info.

[sftl99]

Well, the database is a list of 30,000 parts in a CSV that was given to me already formatted. My entire memory configurator is written with workarounds that have been a sqillion times faster than redoing the actual database. Not to mention, when I get updates on the database I can just import the updates instead of having to go through the CSV and redoing anything. Plus, if the description is 1GB Single Rank, that "Single Rank" is pertinent to the module so I can't take it out and just make it "1GB". I am just going to settle on this one, there's too much to mess with. I am sorting by a different field that suits my needs and looks intentional, just not completely ideal.

[fci]

here is something hacky you can try, although if it doesn't have a space in it, it won't work:
SUBSTRING( a, 1, LOCATE( ' ', a ) -1) (gets the first part of it, then you can use the case statement on it, but if it doesn't have a space, could append that to the end of all the ones that don't have spaces.. mm probably better ideas out there but I'm at work, mmm
edit:
maybe something like this ?

SELECT
foo
FROM
bar
ORDER BY
CASE SUBSTRING( CONCAT(your_field, ' '), 1, LOCATE( ' ', CONCAT(your_field, ' ') -1) 
    WHEN '128MB' THEN 0
    WHEN '256MB' THEN 1
    WHEN '1GB' THEN 2
    ELSE 3  
END
, your_field

[firepages]

... you could just do the right thing & store the data as an integer in MB (or kb if you are adventurous) and translate that later in PHP to human readable.
It will be far far faster for sorting and searching.

[fci]

Originally Posted by firepages

... you could just do the right thing & store the data as an integer in MB (or kb if you are adventurous) and translate that later in PHP to human readable.
It will be far far faster for sorting and searching.

web development has always seemed like this where I've worked.. "Where should/can I cut corners.." and if he has a deadline and the semi-hacky query I provided works.. well, at least he might get a step closer to his deadline (i'm not trying to start any discussion about this, just making a semi off topic comment)

[Kid Charming]

Originally Posted by fci

web development has always seemed like this where I've worked.. "Where should/can I cut corners.." and if he has a deadline and the semi-hacky query I provided works.. well, at least he might get a step closer to his deadline (i'm not trying to start any discussion about this, just making a semi off topic comment)

Perhaps, but in my experience, any time saved by short-cutting your db design process is lost in all the workaround queries you have to figure out later.

[firepages]

I agree with both fci and Kid Charmings last comments , & in this case I think its too easy a fix to ignore , just run a quick script to update the database , drop the MB and * 1000 for the GB ?

[fci]

Originally Posted by Kid Charming

Perhaps, but in my experience, any time saved by short-cutting your db design process is lost in all the workaround queries you have to figure out later.

this is so true

Originally Posted by firepages

I agree with both fci and Kid Charmings last comments , & in this case I think its too easy a fix to ignore , just run a quick script to update the database , drop the MB and * 1000 for the GB ?

mm

this is what he could do (at least to set the initial order on all the products):

UPDATE products 
SET foo_order=(CASE SUBSTRING( CONCAT(the_field , ' '), 1, LOCATE( ' ', CONCAT(the_field , ' ') -1))
            WHEN '128MB' THEN 0
            WHEN '256MB' THEN 1
            WHEN '1GB' THEN 2
            ELSE 3  
END)

everytime he adds a product he'll need to use a similar statement to set the correct order, and if a new case/when pops up he'll have to run the query on the entire table(which is bad but hopefully he sets everything he needs)...
an example insert would look like this (little pseudo code for variables, of course):

INSERT INTO products 
SET (foo_name, foo_order)
VALUES('$foo_name', (CASE SUBSTRING( CONCAT('$foo_name' , ' '), 1, LOCATE( ' ', CONCAT('$foo_name', ' ') -1))
            WHEN '128MB' THEN 0
            WHEN '256MB' THEN 1
            WHEN '1GB' THEN 2
            ELSE 3  
END))