Is it possible in any DBMS or in RDBMS?

  • 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

  • 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

  • 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