none
get max Value Unit from field based on first value before comma separated? RRS feed

  • Question

  • I work on SQL server 2012 I face issue :i can't get first max value from value Unit based on First value before comma .

    as example This value Unit below :

    1.89, 2.625, 3.465

    I will get first value before comma separated as 1.89

    then if this number is max value return full number exist on Value Unit

    sample below :

    create table #finaltable
        (
        partid  int,
        ValueUnit nvarchar(50)
        )
        insert into #finaltable(partid,ValueUnit)
        values
        (2532,'1.71, 2.375, 3.135'),
        (2532,'1.89, 2.625, 3.465')
        select * from #finaltable

    How to get first max value from field Value Unit based on first value before comma separated ?

    Expected Result returned :

    1.89, 2.625, 3.465


    because 1.89 is maximum number from 1.71 then I returned full number

    see image below for clear
    biggest number


    Monday, June 29, 2020 9:35 AM

Answers

  • Well, by simply normalizing your table. It is currently violating 1NF: you must store atomic values.

    Using your unnormalized table:

    DECLARE @finaltable TABLE (
        partid INT ,
        ValueUnit NVARCHAR(50)
    );
    
    INSERT INTO @finaltable ( partid ,
                              ValueUnit )
    VALUES ( 2532, '1.71, 2.375, 3.135' ) ,
           ( 2532, '1.89, 2.625, 3.465' );
    
    SELECT   TOP ( 1 ) FT.partid ,
                       FT.ValueUnit
    FROM     @finaltable FT
             CROSS APPLY ( SELECT SUBSTRING(FT.ValueUnit, 1, CHARINDEX(',', FT.ValueUnit) - 1)) H(FirstValue)
    ORDER BY H.FirstValue DESC;

    Monday, June 29, 2020 11:06 AM

All replies

  • Well, by simply normalizing your table. It is currently violating 1NF: you must store atomic values.

    Using your unnormalized table:

    DECLARE @finaltable TABLE (
        partid INT ,
        ValueUnit NVARCHAR(50)
    );
    
    INSERT INTO @finaltable ( partid ,
                              ValueUnit )
    VALUES ( 2532, '1.71, 2.375, 3.135' ) ,
           ( 2532, '1.89, 2.625, 3.465' );
    
    SELECT   TOP ( 1 ) FT.partid ,
                       FT.ValueUnit
    FROM     @finaltable FT
             CROSS APPLY ( SELECT SUBSTRING(FT.ValueUnit, 1, CHARINDEX(',', FT.ValueUnit) - 1)) H(FirstValue)
    ORDER BY H.FirstValue DESC;

    Monday, June 29, 2020 11:06 AM
  • Less elegant than Stefan's solution

    DECLARE @finaltable TABLE (
        partid INT ,
        ValueUnit NVARCHAR(50)
    );

    INSERT INTO @finaltable ( partid ,
                              ValueUnit )
    VALUES ( 2532, '1.71, 2.375, 3.135' ) ,
           ( 2532, '1.89, 2.625, 3.465' );

    SELECT distinct ValueUnit FROM(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY RN ORDER BY value DESC) rn1
    FROM(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY ValueUnit ORDER BY value DESC) rn FROM @finaltable 
    CROSS APPLY string_split(ValueUnit,',') 
    ) AS Der 
    ) As D WHERE rn1=1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 29, 2020 11:55 AM
    Answerer
  • Uri,

    You cannot use string_split in SQL 2012. And it also would not use the first value from the string in your solution.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Monday, June 29, 2020 5:28 PM
    Moderator
  • Yep I missed that he uses SQL Server 2012, but the query returns first max value...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 30, 2020 4:12 AM
    Answerer
  • I got 2 rows returned:

    DECLARE @finaltable TABLE (
        partid INT ,
        ValueUnit NVARCHAR(50)
    );
    
    INSERT INTO @finaltable ( partid ,
                              ValueUnit )
    VALUES ( 2532, '1.71, 2.375, 3.135, 7.2' ) ,
           ( 2532, '1.89, 2.625, 3.465, 2.1' );
    
    SELECT distinct ValueUnit FROM(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY RN ORDER BY value DESC) rn1
    FROM(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY ValueUnit ORDER BY value DESC) rn FROM @finaltable 
    CROSS APPLY string_split(ValueUnit,',') 
    ) AS Der 
    ) As D WHERE rn1=1


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 4:32 AM
    Moderator
  • Hmm I based on the OP example

    DECLARE @finaltable TABLE (
        partid INT ,
        ValueUnit NVARCHAR(50)
    );

    INSERT INTO @finaltable ( partid ,
                              ValueUnit )
    VALUES ( 2532, '1.71, 2.375, 3.135' ) ,
           ( 2532, '1.89, 2.625, 3.465' );

    SELECT distinct ValueUnit FROM(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY RN ORDER BY value DESC) rn1
    FROM(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY ValueUnit ORDER BY value DESC) rn FROM @finaltable 
    CROSS APPLY string_split(ValueUnit,',') 
    ) AS Der 
    ) As D WHERE rn1=1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 30, 2020 4:39 AM
    Answerer
  • I just added 2 more values to test if it worked just for particular sample or it was a correct solution for any data. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 1:53 PM
    Moderator