Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Apr 2005
    Location
    austin, tx
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ORDER BY, Sorting Question

    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?

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    add a column called order_by and manually set the order .. not sure if there really is any other simple solution.

  • #3
    Regular Coder
    Join Date
    Apr 2005
    Location
    austin, tx
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  • #4
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    mm, after some googling (testing):
    Code:
    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
    Last edited by fci; 02-08-2006 at 04:58 PM.

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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.

  • #6
    Regular Coder
    Join Date
    Apr 2005
    Location
    austin, tx
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by sftl99; 02-08-2006 at 05:20 PM.

  • #7
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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:

    Code:
    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?
    Last edited by fci; 02-08-2006 at 05:26 PM.

  • #8
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Regular Coder
    Join Date
    Apr 2005
    Location
    austin, tx
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #10
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ?
    Code:
    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
    Last edited by fci; 02-08-2006 at 06:26 PM.

  • #11
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,093
    Thanks
    11
    Thanked 101 Times in 99 Posts
    ... 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.
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #12
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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)

  • #13
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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.

  • #14
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,093
    Thanks
    11
    Thanked 101 Times in 99 Posts
    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 ?
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #15
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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

    Quote 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):
    Code:
    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):
    Code:
    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))


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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