1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] What's the best way to select the minimum value from several columns?

Discussão em 'Outras Linguagens' iniciado por Stack, Julho 20, 2021.

  1. Stack

    Stack Membro Participativo

    Given the following table in SQL Server 2005:

    ID Col1 Col2 Col3
    -- ---- ---- ----
    1 3 34 76
    2 32 976 24
    3 7 235 3
    4 245 1 792


    What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium values out of Col1, Col2, and Col 3 for each row)?

    ID Col1 Col2 Col3 TheMin
    -- ---- ---- ---- ------
    1 3 34 76 3
    2 32 976 24 24
    3 7 235 3 3
    4 245 1 792 1


    UPDATE:

    For clarification (as I have said in the coments) in the real scenario the database is properly normalized. These "array" columns are not in an actual table but are in a result set that is required in a report. And the new requirement is that the report also needs this MinValue column. I can't change the underlying result set and therefore I was looking to T-SQL for a handy "get out of jail card".

    I tried the CASE approach mentioned below and it works, although it is a bit cumbersome. It is also more complicated than stated in the answers because you need to cater for the fact that there are two min values in the same row.

    Anyway, I thought I'd post my current solution which, given my constraints, works pretty well. It uses the UNPIVOT operator:

    with cte (ID, Col1, Col2, Col3)
    as
    (
    select ID, Col1, Col2, Col3
    from TestTable
    )
    select cte.ID, Col1, Col2, Col3, TheMin from cte
    join
    (
    select
    ID, min(Amount) as TheMin
    from
    cte
    UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
    ) as minValues
    on cte.ID = minValues.ID


    I'll say upfront that I don't expect this to offer the best performance, but given the circumstances (I can't redesign all the queries just for the new MinValue column requirement), it is a pretty elegant "get out of jail card".

    Continue reading...

Compartilhe esta Página