Web Analytics Made Easy -
StatCounter Frustrating MySQL sorting issue - CodingForum

Announcement

Collapse
No announcement yet.

Frustrating MySQL sorting issue

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Frustrating MySQL sorting issue

    I have a list of stock numbers to sort. Finding it frustrating to do "natural sorting" with MySQL (or maybe that's not what this is called)

    'stock' is the name of my column of stock numbers.

    This is the result when I do this:
    $query = "SELECT * FROM cards WHERE cato='$cat' ORDER BY length(stock),stock ASC";
    ===================================================================================
    I1
    I2
    I3
    I4
    W1
    W2
    W3
    W10
    W11
    I12
    I13
    I14
    I15
    I20
    IC1
    IC2
    IC3

    This is the way I would like them to be sorted:
    (letters first, then by number) and the lengths will vary.
    ==========================================
    I1
    I2
    I3
    I4
    I12
    I13
    I14
    I15
    I20
    IC1
    IC2
    IC3
    W1
    W2
    W3
    W10
    W11

    Another person said do this:
    $query = "SELECT * FROM cards WHERE cato='$cat' ORDER BY stock+0, stock";
    Here is what happened ... still not right.
    ==========================================================================
    I1
    I12
    I13
    I14
    I15
    I2
    I20
    I3
    I4
    IC1
    IC2
    IC3
    W1
    W10
    W11
    W2
    W3

  • #2
    That should really be two separate fields if you want to be able to treat the number portion separate - there is no easy way to split the field for ordering as the spot to split it isn't always in the same place.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

    Comment


    • #3
      Here ya go Mr. OP.


      Code:
      SELECT
      	stock
      FROM
      	cards
      ORDER BY 
        IF(stock REGEXP '^[A-Z]',
          CONCAT(
            LEFT(stock, 1), 
            LPAD(SUBSTRING(stock, 2), 20, '0')),
          CONCAT(
            '@',
            LPAD(stock, 20, '0')))
      To save time, lets just assume I am almost never wrong.

      The XY Problem
      The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

      Make A Donation https://www.paypal.me/KevinRubio

      Comment


      • #4
        Regular expressions inside an sql statement... Benanamen I bow down to your geniousness - I am not worthy
        "Tango says double quotes with a single ( ' ) quote in the middle"
        '$Name says single quotes with a double ( " ) quote in the middle'
        "Tango says double quotes ( \" ) must escape a double quote"
        '$Name single quotes ( \' ) must escape a single quote'

        Comment


        • #5
          wow.

          The fact that I can't wrap my head around any of what benanamen's query does ... but it works perfectly!

          Much Thanks!

          Comment


          • #6
            That's the sort of complexity that can be needed with compound fields like that - if the char and number parts were separate then the query would be much simpler and the code would run much faster.
            Stephen
            Learn Modern JavaScript - http://javascriptexample.net/
            Helping others to solve their computer problem at http://www.felgall.com/

            Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

            Comment


            • #7
              Originally posted by felgall View Post
              That's the sort of complexity that can be needed with compound fields like that - if the char and number parts were separate then the query would be much simpler and the code would run much faster.
              Depending on what the data is you may not be able to separate characters from numbers. Take for example a Ford part number - CV6Z9601A
              To save time, lets just assume I am almost never wrong.

              The XY Problem
              The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

              Make A Donation https://www.paypal.me/KevinRubio

              Comment


              • #8
                @felgall,
                I just read about having 2 columns, one for the alpha, the other for the integer. Keep them separate but then sort them and combine them whenever they need to be displayed. Because the client's stock number scheme is so simple and there are not that many items, I thought it would be easier to just keep them as-is (until of course I tried sorting). So that query is perfectly fine for this case. Upon Googling this topic, I see that many people struggle with the same thing. Date and Time functions seem to be similar ... people had so much trouble working with them that special functions were made just to help out. Maybe PHP needs a "stock" function (mkstock, strtostock) LOL.

                Comment


                • #9
                  Originally posted by mlseim View Post
                  @felgall,
                  I just read about having 2 columns, one for the alpha, the other for the integer. Keep them separate but then sort them and combine them whenever they need to be displayed. Because the client's stock number scheme is so simple and there are not that many items, I thought it would be easier to just keep them as-is (until of course I tried sorting). So that query is perfectly fine for this case. Upon Googling this topic, I see that many people struggle with the same thing. Date and Time functions seem to be similar ... people had so much trouble working with them that special functions were made just to help out. Maybe PHP needs a "stock" function (mkstock, strtostock) LOL.
                  Date and time are completely different - the database uses its own internal format to hold them in minimal characters - the functions allow for the conversion between the internal format and the display format required.

                  Similar functions do exist for handling stock codes consisting of an alpha and then a numeric part - you can use them to split the stock code into two fields for its internal database format and to reconstruct them back into one field on retrieval without needing to change anything other than the SQL.
                  Stephen
                  Learn Modern JavaScript - http://javascriptexample.net/
                  Helping others to solve their computer problem at http://www.felgall.com/

                  Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

                  Comment

                  Working...
                  X