Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: Max output for varchar

    Code:
    SELECT receipt_no,waybill_no FROM `ticket_data` WHERE `waybill_no` = '0000000028' AND `ticket_code` = 'L'  order by CAST(MID(LTRIM(`receipt_no`),3) AS UNSIGNED) ;
    Results shows as below and "L-" can cary in length it can be "LM-" also

    +------------+------------+
    | receipt_no | waybill_no |
    +------------+------------+
    | L-865 | 0000000028 |
    | L-866 | 0000000028 |
    | L-867 | 0000000028 |


    I want the output for maximum receipt_no as (L-Maximum number)

    +------------+------------+
    |Max receipt_no|waybill_no |
    +------------+------------+
    | L-867 | 0000000028 |

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll use the MySQL definition for maximum, but your definition may vary.
    Code:
    SELECT Max(receipt_no) AS receipt_no, waybill_no
       FROM ticket_data
       GROUP BY waybill_no
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2007
    Posts
    197
    Quote Originally Posted by Pat Phelan View Post
    I'll use the MySQL definition for maximum, but your definition may vary.
    Code:
    SELECT Max(receipt_no) AS receipt_no, waybill_no
       FROM ticket_data
       GROUP BY waybill_no
    -PatP
    While I use max(receipt_no) the result is coming wrong

    as L-801 will show maximum value for L-1000 as this is varchar but this is wrong result

    I used
    Code:
    SELECT MAX(CAST(MID(LTRIM(`receipt_no`),length(ticket_code)+2) AS UNSIGNED)),length(ticket_code),waybill_no FROM `ticket_data` WHERE `waybill_no` = '0000000028' AND `ticket_code` = 'L'
    I First changed values to integer the applied max

Posting Permissions

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