how to return max from a varchar column

  • Hi,

    I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example

    1.0.0

    1.0.1

    1.1.0

    1.2.1

    2.0.0

    2.1.1

    etc

    These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.

    Does anyone have a script which will easily do this please?

  • Tex-166085 (1/21/2015)


    Hi,

    I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example

    1.0.0

    1.0.1

    1.1.0

    1.2.1

    2.0.0

    2.1.1

    etc

    These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.

    Does anyone have a script which will easily do this please?

    Quick solution (if I'm getting this right)

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @SAMPLE_DATA TABLE

    (

    THREE_PART_NUM VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED

    );

    INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)

    VALUES

    ('1.0.0')

    ,('1.0.1')

    ,('1.1.0')

    ,('1.2.1')

    ,('2.0.0')

    ,('2.1.1')

    ;

    SELECT

    MAX(SD.THREE_PART_NUM)

    FROM @SAMPLE_DATA SD;

    Results

    2.1.1

  • Eirikur Eiriksson (1/21/2015)


    Tex-166085 (1/21/2015)


    Hi,

    I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example

    1.0.0

    1.0.1

    1.1.0

    1.2.1

    2.0.0

    2.1.1

    etc

    These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.

    Does anyone have a script which will easily do this please?

    Quick solution (if I'm getting this right)

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @SAMPLE_DATA TABLE

    (

    THREE_PART_NUM VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED

    );

    INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)

    VALUES

    ('1.0.0')

    ,('1.0.1')

    ,('1.1.0')

    ,('1.2.1')

    ,('2.0.0')

    ,('2.1.1')

    ;

    SELECT

    MAX(SD.THREE_PART_NUM)

    FROM @SAMPLE_DATA SD;

    Results

    2.1.1

    add one more sample and see what will happen:

    ('12.1.1')

    ...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • If your data always three-parts you can do:

    SELECT TOP 1 THREE_PART_NUM

    FROM @SAMPLE_DATA SD

    ORDER BY CAST(PARSENAME(THREE_PART_NUM,3) AS INT) DESC

    ,CAST(PARSENAME(THREE_PART_NUM,2) AS INT) DESC

    ,CAST(PARSENAME(THREE_PART_NUM,1) AS INT) DESC;

    if it is possible to have one part or two parts records eg. 10 or 10.1 then a bit more "complicated":

    SELECT TOP 1 THREE_PART_NUM

    FROM @SAMPLE_DATA SD

    ORDER BY ISNULL(CAST(PARSENAME(THREE_PART_NUM,3) AS INT),2147483647) DESC

    ,ISNULL(CAST(PARSENAME(THREE_PART_NUM,2) AS INT),2147483647) DESC

    ,ISNULL(CAST(PARSENAME(THREE_PART_NUM,1) AS INT),2147483647) DESC;

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Try the following

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @test-2 TABLE

    (

    Task VARCHAR(10) NOT NULL

    );

    INSERT INTO @test-2 (Task)

    VALUES

    ('1.0.0')

    ,('1.0.1')

    ,('1.1.0')

    ,('1.2.1')

    ,('2.0.0')

    ,('2.1.1')

    ,('12.1.1')

    ;

    SELECT MAX(CAST(REPLACE(Task,'.','')AS BIGINT)) FROM @test-2

  • Eugene Elutin (1/21/2015)


    add one more sample and see what will happen:

    ('12.1.1')

    ...

    Elementary;-)

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @SAMPLE_DATA TABLE

    (

    THREE_PART_NUM VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED

    );

    INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)

    VALUES

    ('1.0.0')

    ,('1.0.1')

    ,('1.1.0')

    ,('1.2.1')

    ,('2.0.0')

    ,('2.1.1')

    ,('12.1.1')

    ;

    SELECT

    MAX(CONVERT(BIGINT,REPLACE(SD.THREE_PART_NUM,CHAR(46),CHAR(48)),0))

    FROM @SAMPLE_DATA SD;

    Returns

    120101

  • ...

    Elementary;-)

    😎

    Not quite so, try ('1.0.100')

    😉

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi,

    wow, thanks for all the replies, I will work through them in my lunch hour. Yes, I cannot say how high each of the digits will go. It could be up to three, so 100.100.200 may be possible. I don't want to exclude the possibility, although that would be extreme.and extremely unlikely to go into 4 figures for each

    Thanks again

  • Eugene Elutin (1/21/2015)


    ...

    Elementary;-)

    😎

    Not quite so, try ('1.0.100')

    😉

    :pinch:

  • Tex-166085 (1/21/2015)


    Hi,

    wow, thanks for all the replies, I will work through them in my lunch hour. Yes, I cannot say how high each of the digits will go. It could be up to three, so 100.100.200 may be possible. I don't want to exclude the possibility, although that would be extreme.and extremely unlikely to go into 4 figures for each

    Thanks again

    PARSENAME will work maximum for 4 parts.

    If more parts will be involved, you will need to split string into parts by other means (check one of the best splitter functions implementations here: http://www.sqlservercentral.com/articles/Tally+Table/72993/)

    Just note: If you want proper order, you need to treat each part as numeric separately or you need to reformat your string to something like:

    0000.0000.0000

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • This is possibly an overkill but can easily be extended

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @SAMPLE_DATA TABLE

    (

    THREE_PART_NUM VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED

    );

    INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)

    VALUES

    ('1000000001.0.00.0012345.012345.987654')

    ,('1000000001.0.10.1012345.012345.987654')

    ,('1000000001.1.00.0012345.012345.987654')

    ,('1000000001.2.10.1012345.012345.987654')

    ,('2000000001.0.00.0012345.012345.987654')

    ,('2000000001.1.10.1012345.012345.987654')

    ,('3147483647.1.10.1012345.012345.987654')

    ,('3147483647.11.0.1012345.012345.987654')

    ,('1000000000.0.10.1012345.012345.987654')

    ,('1000000002.1.10.1012345.012345.987654')

    ;

    SELECT TOP (1)

    SD.THREE_PART_NUM

    ,MAX(CASE WHEN X.ItemNumber = 1 THEN CONVERT(BIGINT,X.Item,0) END) AS C1

    ,MAX(CASE WHEN X.ItemNumber = 2 THEN CONVERT(BIGINT,X.Item,0) END) AS C2

    ,MAX(CASE WHEN X.ItemNumber = 3 THEN CONVERT(BIGINT,X.Item,0) END) AS C3

    ,MAX(CASE WHEN X.ItemNumber = 4 THEN CONVERT(BIGINT,X.Item,0) END) AS C4

    ,MAX(CASE WHEN X.ItemNumber = 5 THEN CONVERT(BIGINT,X.Item,0) END) AS C5

    ,MAX(CASE WHEN X.ItemNumber = 6 THEN CONVERT(BIGINT,X.Item,0) END) AS C6

    FROM @SAMPLE_DATA SD

    OUTER APPLY dbo.DelimitedSplit8K(SD.THREE_PART_NUM,CHAR(46)) AS X

    GROUP BY SD.THREE_PART_NUM

    ORDER BY C1 DESC,C2 DESC,C3 DESC,C4 DESC,C5 DESC,C6 DESC;

    Results

    THREE_PART_NUM C1 C2 C3 C4 C5 C6

    -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------

    3147483647.11.0.1012345.012345.987654 3147483647 11 0 1012345 12345 987654

  • What about some change makes it to be 7-parts? Then, few months later, 8-parts?

    If you want to be dynamic and a bit more simpler, do this:

    SELECT TOP 1 SD.THREE_PART_NUM

    FROM @SAMPLE_DATA SD

    ORDER BY (SELECT RIGHT('0000000000' + x.item,10) + '.'

    FROM dbo.DelimitedSplit8K(SD.THREE_PART_NUM,CHAR(46)) AS X

    ORDER BY x.ItemNumber

    FOR XML PATH('')) DESC

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

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