#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Location
    kerala, India
    Posts
    81
    Rep Power
    2

    Ordering Numbers


    Hi,

    I used mysql query "SELECT DISTINCT productname FROM tablename GROUP BY productname" for getting products from table.Result is

    "A4 Heavy Cover Booklets 12pp
    A4 Heavy Cover Booklets 16pp
    A4 Heavy Cover Booklets 20pp
    A4 Heavy Cover Booklets 24pp
    A4 Heavy Cover Booklets 28pp
    A4 Heavy Cover Booklets 80pp
    A4 Heavy Cover Booklets 8pp "


    But, How can we get results starting with "A4 Heavy Cover Booklets 8pp"?

    Example:
    A4 Heavy Cover Booklets 8pp
    A4 Heavy Cover Booklets 12pp
    A4 Heavy Cover Booklets 16pp
    A4 Heavy Cover Booklets 20pp
    A4 Heavy Cover Booklets 24pp
    A4 Heavy Cover Booklets 28pp
    A4 Heavy Cover Booklets 80pp

    Please help me.


    Thanks
  2. #2
  3. kill 9, $$;
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2001
    Location
    Shanghai, An tSín
    Posts
    6,898
    Rep Power
    3887
    This is an SQL question rather than a PHP one so I've moved it to the appropriate forum.
    Last edited by ishnid; March 19th, 2013 at 09:43 AM.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Iran
    Posts
    149
    Rep Power
    140
    Why not adding an ORDER BY clause directly to your query?

    Here is an example based on your sample data:
    Code:
    mysql> SELECT productName
        -> FROM
        -> (
        ->     SELECT 'A4 Heavy Cover Booklets 12pp' AS productName FROM DUAL UNION ALL
        ->     SELECT 'A4 Heavy Cover Booklets 16pp' AS productName FROM DUAL UNION ALL
        ->     SELECT 'A4 Heavy Cover Booklets 20pp' AS productName FROM DUAL UNION ALL
        ->     SELECT 'A4 Heavy Cover Booklets 24pp' AS productName FROM DUAL UNION ALL
        ->     SELECT 'A4 Heavy Cover Booklets 28pp' AS productName FROM DUAL UNION ALL
        ->     SELECT 'A4 Heavy Cover Booklets 80pp' AS productName FROM DUAL UNION ALL
        ->     SELECT 'A4 Heavy Cover Booklets 8pp' AS productName FROM DUAL
        -> ) AS products_tab
        -> GROUP BY productName
        -> ORDER BY productName DESC;
    +------------------------------+
    | productName                  |
    +------------------------------+
    | A4 Heavy Cover Booklets 8pp  |
    | A4 Heavy Cover Booklets 80pp |
    | A4 Heavy Cover Booklets 28pp |
    | A4 Heavy Cover Booklets 24pp |
    | A4 Heavy Cover Booklets 20pp |
    | A4 Heavy Cover Booklets 16pp |
    | A4 Heavy Cover Booklets 12pp |
    +------------------------------+
    7 rows in set (0.00 sec)
    
    mysql>

    Regards,
    Dariyoosh
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Location
    kerala, India
    Posts
    81
    Rep Power
    2
    Hi dariyoosh,

    Thanks for you reply.Actually my client need
    "A4 Heavy Cover Booklets 8pp
    A4 Heavy Cover Booklets 12pp
    A4 Heavy Cover Booklets 16pp
    A4 Heavy Cover Booklets 20pp
    A4 Heavy Cover Booklets 24pp
    A4 Heavy Cover Booklets 28pp
    A4 Heavy Cover Booklets 80pp"
    this result.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Location
    kerala, India
    Posts
    81
    Rep Power
    2
    Hi ishnid,

    This is not an SQL base question.It may be PHP.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    your actual problem is that your table structure is broken. You have different data all crammed into one big string. This means that the data is basically inaccessible to the database system -- which brings up the question why you even use a relational database system.

    Sure, you can do some workarounds with PHP. But that's just ugly, slow, and you'll fight the same problems again and again (as you can see in your previous posts).

    If you have any chance to fix the structure, do it. Look up "normalization", specifically "first normal form". That's the only real solution. Otherwise, well, prepare for an endless fight and many, many ugly workarounds that will kill the performance. You'll have to put all rows into an array and then call usort on it. The custom compare function has to extract each number with a regex and then sort by that.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by dariyoosh
    Code:
        -> ORDER BY productName DESC;
    
    | A4 Heavy Cover Booklets 8pp  |
    | A4 Heavy Cover Booklets 80pp |
    because ORDER BY isn't good enough

    you said DESC, so 8pp should've come last instead of first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Sarath_PHP
    This is not an SQL base question.It may be PHP.
    it's definitely not a php problem

    it's actually a bad data problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Sarath_PHP
    SELECT DISTINCT productname FROM tablename GROUP BY productname
    just a note for your future reference...

    GROUP BY produces unique results, so DISTINCT here is redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Right() and left()


    Although I am a newbie to SQL, my instinct from other environments is that you might be able to solve the problem by first grabbing the four rightmost characters of the data using right(data,4) and then grab the left two characters of that, such as left(right(data,4),2)

    I have often had to do this sort of disecting and data cleanup in Excel when people feed me crappy data.

    Of course, since the data you gave us is only a sample, I realize the required cleanup could be more involved.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Location
    kerala, India
    Posts
    81
    Rep Power
    2
    Hi Finance Newbie,

    Thanks i got it. I include right() in to mysql query.

    "SELECT productname FROM tablename GROUP BY productname ORDER BY RIGHT( productname, 4)"

    Then my result is:

    A4 Heavy Cover Booklets 8pp
    A4 Heavy Cover Booklets 12pp
    A4 Heavy Cover Booklets 16pp
    A4 Heavy Cover Booklets 20pp
    A4 Heavy Cover Booklets 24pp
    A4 Heavy Cover Booklets 28pp
    A4 Heavy Cover Booklets 80pp
  22. #12
  23. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Iran
    Posts
    149
    Rep Power
    140
    Originally Posted by r937
    because ORDER BY isn't good enough

    you said DESC, so 8pp should've come last instead of first
    Thanks for this remark, yes in fact I made a mistake and my solution doesn't fulfil the OP's requirement.


    Regards,
    Dariyoosh
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by Sarath_PHP
    Hi Finance Newbie,

    Thanks i got it. I include right() in to mysql query.

    "SELECT productname FROM tablename GROUP BY productname ORDER BY RIGHT( productname, 4)"

    Then my result is:

    A4 Heavy Cover Booklets 8pp
    A4 Heavy Cover Booklets 12pp
    A4 Heavy Cover Booklets 16pp
    A4 Heavy Cover Booklets 20pp
    A4 Heavy Cover Booklets 24pp
    A4 Heavy Cover Booklets 28pp
    A4 Heavy Cover Booklets 80pp
    Also, I pointed to the complex solution but forgot to think about the simple solution: If the problem is only occurring once AND you can play with the source data, maybe just go in and add an additional space or a zero in front of the eight.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Location
    kerala, India
    Posts
    81
    Rep Power
    2
    Originally Posted by r937
    just a note for your future reference...

    GROUP BY produces unique results, so DISTINCT here is redundant


    Thank you so much.

IMN logo majestic logo threadwatch logo seochat tools logo