Last post Jul 22, 2019 01:36 PM by limno
Jul 20, 2019 03:44 AM|Gopi.MCA|LINK
This is my table 1 data
I want Resultset like this below, Get Each Item Whos Price Is Highest
Jul 20, 2019 12:25 PM|mgebhard|LINK
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.
Jul 22, 2019 08:38 AM|Yang Shen|LINK
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.
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:
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)
SELECT @collist += '(''[' + COLUMN_NAME + ']'',' + '['+COLUMN_NAME + ']),'
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)
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)
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+''''
select * from #SP
This will give you below result:
Jul 22, 2019 01:36 PM|limno|LINK
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: