Post reply

How to select highest number ib a series

  • July 5, 2011 at 9:14 am

    #243766

    Hello,

    I'm definitely a newbie. I have a table that has over 100,000 records in it. We use account numbers with 10 numbers or digits. The 8 digits on the left would be tied to an address. The 2 digits on the right would be a tenant number. Could someone tell me how to find the highest NUMBERS?

    A sample would look like this: 1212345601

    1212345602

    1212345603

    1412345601

    1412345602

    I would be trying to get 1212345603 and 1412345602.

    Thanks,

  • July 5, 2011 at 9:23 am

    #1348356

    Maybe this?

    DECLARE @t TABLE(AccNo VARCHAR(10))

    INSERT INTO @t(AccNo)

    SELECT '1212345601' UNION ALL

    SELECT '1212345602' UNION ALL

    SELECT '1212345603' UNION ALL

    SELECT '1412345601' UNION ALL

    SELECT '1412345602';

    SELECT MAX(AccNo) AS AccNo

    FROM @t

    GROUP BY LEFT(AccNo,8)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 5, 2011 at 9:30 am

    #1348364

    Thanks, it worked great.

  • July 5, 2011 at 9:31 am

    #1348365

    If I read the original question correctly, I think he wants the highest number per account/address (the first eight digits). Maybe something like:

    CREATE TABLE #MyTempTbl_01 (WholeId varchar(10))

    INSERT #MyTempTbl_01 (WholeId) VALUES ('1212345602')

    INSERT #MyTempTbl_01 (WholeId) VALUES ('1212345603')

    INSERT #MyTempTbl_01 (WholeId) VALUES ('1412345601')

    INSERT #MyTempTbl_01 (WholeId) VALUES ('1412345602')

    SELECT *

    FROM #MyTempTbl_01

    SELECT LEFT(CAST(WholeId AS char(10)), 8) AS Address,

    RIGHT(CAST(WholeId AS char(10)), 2) AS OrderNum

    FROM #MyTempTbl_01

    SELECT LEFT(CAST(WholeId AS char(10)), 8),

    MAX(RIGHT(CAST(WholeId AS char(10)), 2))

    FROM #MyTempTbl_01

    GROUP BY LEFT(CAST(WholeId AS char(10)), 8)

    HTH,

    Rob

  • July 5, 2011 at 12:25 pm

    #1348516

    Thanks again.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply