Results 1 to 6 of 6

Thread: MAX function

  1. #1
    Join Date
    Oct 2005
    Posts
    22

    Unanswered: MAX function

    I have a field called 'CustCode' with the following (example) data:

    ABB0001
    ABC0001
    ABC0002
    FGH0001
    KML0001
    JNI0001
    JNI0002

    ...and the following code which is supposed to bring back 1 row with the highest number in it, but if I ran it for the above data it would bring back the 2 rows for the 'ABC...' records.

    SELECT MAX(RIGHT(CustCode, 4)) AS MCODE
    FROM Customers
    WHERE LEFT(CUSTCODE, 3) = 'ABC'
    GROUP BY CUSTCODE

    Can someone tell me if what I'm trying to achieve is possible. And if it is, what's wrong with my SQL code?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @t99 table(CustCode varchar(10))
    
    INSERT INTO @t99(CustCode)
    SELECT 'ABB0001' UNION ALL
    SELECT 'ABC0001' UNION ALL
    SELECT 'ABC0002' UNION ALL
    SELECT 'FGH0001' UNION ALL
    SELECT 'KML0001' UNION ALL
    SELECT 'JNI0001' UNION ALL
    SELECT 'JNI0002'
    
      SELECT MAX(RIGHT(CustCode, 4)) AS MCODE
        FROM @t99
       WHERE LEFT(CUSTCODE, 3) = 'ABC'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2005
    Posts
    22
    Thanks r937.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    yes...Thank you Rudy....

    Now, care to explain how this is useful?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    yes...Thank you Rudy....

    Now, care to explain how this is useful?
    sure thing, brett

    it is useful because it answers the original question

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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