locked
Get Highest Value Query RRS feed

  • Question

  • User-807418713 posted

    Hi

    This is my table 1 data

    Item Name Supplier1 Price Suppllier 2 Price Supplier 3 Price Supplier 4 Price
    Item1 100 55 20 25
    Item2 10 50 30 35
    Item3 5 20 25 15
    Item4 200 150 50 150

    I want Resultset like this below, Get Each Item Whos Price Is Highest

    Item Name Supplier1 Price Suppllier 2 Price Supplier 3 Price Supplier 4 Price This Below Supplier Is Higher
    Item1 100 55 20 25 Supplier 1
    Item2 10 50 30 35 Supplier 2
    Item3 5 25 20 15 Supplier 2
    Item4 200 150 50 400 Supplier 4

    Thanking You

    Saturday, July 20, 2019 3:44 AM

Answers

  • User-719153870 posted

    Hi Gopi.MCA,

    Generally, according to the table you provide, it is not suitable to get the result set you want.

    Because in your table, supplier is a column name, not a value.

    You can only get the highest value of the four columns, not the column name which has the highest value.

    for example:

    select *,(select max(sup) from (values([Supplier 1 Price]),([Supplier 2 Price]),([Supplier 3 Price]),([Supplier 4 Price])) as supp (sup)) as [This Below Supplier Is Higher] from  Supplier 

    which you can get:

    If you want to forcibly find the corresponding column name by value, it is also possible. Please refer to:Here.

    But this is not recommended, better table structure design will make your query easier.

    Please refer to below SQL codes:

    use DatabaseTestPool
    go
    select (select max(sup) from (values([Supplier 1 Price]),([Supplier 2 Price]),([Supplier 3 Price]),([Supplier 4 Price])) as supp (sup)) as [This Below Supplier Is Higher] into #Sup from  Supplier 
    select * into #SP from (select * from Supplier)s
    alter table #SP add [This Below Supplier Is Higher] varchar(50)
    
    declare @price int
    declare @i int
    declare @collist VARCHAR(max)=''
    declare @col NVARCHAR(max)
    declare @name nvarchar(50)
    declare @sql NVARCHAR(max)
    
    set @i=1
    SELECT @collist += '(''[' + COLUMN_NAME + ']'',' + '['+COLUMN_NAME + ']),'
    FROM   INFORMATION_SCHEMA.columns
    WHERE  TABLE_NAME = 'Supplier'
           AND COLUMN_NAME LIKE 'Supplier%'
           AND TABLE_SCHEMA = 'dbo'
    
    SELECT @collist = LEFT(@collist, Len(@collist) - 1)
    
    while @i<=(select COUNT(*) from #Sup)
    begin
    set @price=(select top 1 [This Below Supplier Is Higher] from #Sup where [This Below Supplier Is Higher] not in (select top(@i-1) [This Below Supplier Is Higher] from #Sup))
    set @name=(select top 1 [Item Name] from #SP where [Item Name] not in (select top(@i-1) [Item Name] from #SP))
    SET @col ='
    SELECT top 1 right(left(Cname,11),10)
    FROM   Supplier
           CROSS apply (VALUES' + @collist
              + ') ca (cname, data)
    WHERE  data = '+convert(varchar(50),@price)
    
    set @sql='update #SP set [This Below Supplier Is Higher] = ('+@col+') where [Item Name] ='''+@name+''''
    exec(@sql)
    set @i+=1
    end
    select * from #SP

    This will give you below result:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2019 8:38 AM

All replies

  • User475983607 posted

    This is a very easy requirement if the table schema is normalized.  Fix the schema first where each supplier is a record not a row.

    Saturday, July 20, 2019 12:25 PM
  • User-719153870 posted

    Hi Gopi.MCA,

    Generally, according to the table you provide, it is not suitable to get the result set you want.

    Because in your table, supplier is a column name, not a value.

    You can only get the highest value of the four columns, not the column name which has the highest value.

    for example:

    select *,(select max(sup) from (values([Supplier 1 Price]),([Supplier 2 Price]),([Supplier 3 Price]),([Supplier 4 Price])) as supp (sup)) as [This Below Supplier Is Higher] from  Supplier 

    which you can get:

    If you want to forcibly find the corresponding column name by value, it is also possible. Please refer to:Here.

    But this is not recommended, better table structure design will make your query easier.

    Please refer to below SQL codes:

    use DatabaseTestPool
    go
    select (select max(sup) from (values([Supplier 1 Price]),([Supplier 2 Price]),([Supplier 3 Price]),([Supplier 4 Price])) as supp (sup)) as [This Below Supplier Is Higher] into #Sup from  Supplier 
    select * into #SP from (select * from Supplier)s
    alter table #SP add [This Below Supplier Is Higher] varchar(50)
    
    declare @price int
    declare @i int
    declare @collist VARCHAR(max)=''
    declare @col NVARCHAR(max)
    declare @name nvarchar(50)
    declare @sql NVARCHAR(max)
    
    set @i=1
    SELECT @collist += '(''[' + COLUMN_NAME + ']'',' + '['+COLUMN_NAME + ']),'
    FROM   INFORMATION_SCHEMA.columns
    WHERE  TABLE_NAME = 'Supplier'
           AND COLUMN_NAME LIKE 'Supplier%'
           AND TABLE_SCHEMA = 'dbo'
    
    SELECT @collist = LEFT(@collist, Len(@collist) - 1)
    
    while @i<=(select COUNT(*) from #Sup)
    begin
    set @price=(select top 1 [This Below Supplier Is Higher] from #Sup where [This Below Supplier Is Higher] not in (select top(@i-1) [This Below Supplier Is Higher] from #Sup))
    set @name=(select top 1 [Item Name] from #SP where [Item Name] not in (select top(@i-1) [Item Name] from #SP))
    SET @col ='
    SELECT top 1 right(left(Cname,11),10)
    FROM   Supplier
           CROSS apply (VALUES' + @collist
              + ') ca (cname, data)
    WHERE  data = '+convert(varchar(50),@price)
    
    set @sql='update #SP set [This Below Supplier Is Higher] = ('+@col+') where [Item Name] ='''+@name+''''
    exec(@sql)
    set @i+=1
    end
    select * from #SP

    This will give you below result:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2019 8:38 AM
  • User77042963 posted

    If you don't learn the basic of relational database concept, you will stay here to ask this type questions forever.

    Please take some time to learn the basic before you are struggling for a query in a wrong design.

    This is the same issue as your previous one. Only you can help yourself by design a normalized table first.

    Here is you previous question:

    https://forums.asp.net/t/2157893.aspx?Production+Plan+Each+20+Quantity+Per+Week+

    Monday, July 22, 2019 1:36 PM