• dwain.c (10/21/2013)


    Illustrating what PietLinden suggested, which I like because it avoids the sort inherent in DISTINCT:

    WITH SampleData AS

    (

    SELECT invoice_no

    FROM

    (

    VALUES ('897456-0001')

    ,('897456-0002')

    ,('897456-0003')

    ,('898657-0001')

    ,('898657-0004')

    ,('889977-0003')

    ,('889978-0002')

    ,('889978-0004')

    ,('889979-0001')

    ,('889979-0010')

    ,('889995-0002')

    ,('889995-0003')

    ,('889995-0004')

    ) a (invoice_no)

    )

    SELECT invoice_no=MAX(invoice_no)

    FROM SampleData

    GROUP BY LEFT(invoice_no, 6);

    I was actually going to come back and try PietLinden's suggestion as well. I always like learning multiple ways to complete a task. Thanks for your input.