March 10, 2011 at 6:07 am
Hi,
I have following table and Data.
USE tempdb
GO
CREATE TABLE Product
(ProdID Int
,ProductName Varchar(50)
,Unit Varchar(10)
,Prize1 Money
,Prize2 Money
,Prize3 Money
)
GO
INSERT Product VALUES(1,'Product1','NOS',120.5,200,150)
,(2,'Product2','NOS',10.5,25,12)
GO
--------------------------------------------------------------------
I want following query with Product Table
Query #1 "SELECT *, Max(Prize1, Prize2, Prize3) AS MaxPrize FROM Product"
Query #2 after 1 query I want to know in which Prize column the Max values have.
I know I am breaking RDBMS Normalization rules. but I just need to know is it possible in any Database system.
Ram
MSSQL DBA
March 10, 2011 at 6:49 am
I don't think you'll get the column name,
but the ordinal can be retrieved using a udf
Don't shoot me if this doesn't perform well, chances are a clr fn can do this way faster.
There may be a number of ways to perform this better.
I didn't test this on a big volume.
create function ufn_MaxPrize
( @Prize1 money
, @Prize2 money
, @Prize3 money
)
returns @tb table
( MaxPrize money
, MaxPrizeColNo tinyint
)
as
begin
declare @tvList table
( ColNo int not null
, Prize money
)
insert into @tvList
values ( 1, @Prize1 ),
( 2, @prize2 ),
( 3, @prize3 ) ;
with cteM
as (
Select MAX(Prize) MaxPrize
from @tvList
)
insert into @tb
select M.MaxPrize
, T.ColNo
from cteM M
cross apply @tvList T
where T.Prize = M.MaxPrize
return
end
go
--SELECT *, Max(Prize1, Prize2, Prize3) AS MaxPrize FROM Product ;
SELECT P.*
, M.maxprize
, M.MaxPrizeColNo
FROM Product P
outer apply dbo.ufn_MaxPrize(Prize1, Prize2, Prize3) M ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2011 at 7:07 am
I did this in SQL 2008. Used your table create and populate script, then:
;with Prizes as
(select *, ROW_NUMBER() over (partition by ProductName, Unit order by Value desc) as Row
from (select ProductName, Unit, Prize1, Prize2, Prize3
from dbo.Product) as Src
unpivot
(Value for Prize in (Prize1, Prize2, Prize3)) as Unpiv)
select *
from Prizes
where Row = 1;
See if that gives you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply