CODEWITHSUNDEEP
sqlsql-server
ocs
ocs

Reputation: 43

MSSQL select lowest but not NULL/zero value from 25 columns

I have 25 (numeric) columns in one table in MSSQL and I need select lowest value, but not NULL or 0 value.

Columns are named like "%_price" (aaa_price, bbb_price, ccc_price...). Some columns contains 0 or NULL value.

Example:

table (aaa_price, bbb_price, ccc_price, ddd_price, eee_price, fff_price)
value (NULL,      0,         324.23,    162.50,    NULL,      1729.72  )

Right result: 162.50

I can use some "brute force" method like:

SELECT CASE
    WHEN Col1 <= Col2 AND Col1 <= Col3 AND Col1 <= Col4 AND Col1 <= Col5 THEN Col1
    WHEN                  Col2 <= Col3 AND Col2 <= Col4 AND Col2 <= Col5 THEN Col2
    WHEN                                   Col3 <= Col4 AND Col3 <= Col5 THEN Col3
    WHEN                                                    Col4 <= Col5 THEN Col4
    ELSE                                                                      Col5
END AS [Min Value] FROM [Your Table]

But its insane with 25 columns... is there any better solution?

Thank You!

Upvotes: 2

Views: 898

Answers (3)

uzi
uzi

Reputation: 4146

Cross apply can be good option in this case:

select
    *
from
    myTable
    cross apply (select 
                    minVal = min(val) 
                from (
                    values (aaa_price),(bbb_price),(...)
                ) t(val) where val > 0
    ) q

Edit: You have to use dynamic SQL if you want to get column names from INFORMATION_SCHEMA.COLUMNS table.

declare @sql varchar(8000)
declare @cols varchar(8000)

select @cols =
    stuff((
        SELECT 
            ',(' + COLUMN_NAME + ')'
        FROM 
            INFORMATION_SCHEMA.COLUMNS 
        WHERE 
            TABLE_NAME = 'mytable' 
            AND TABLE_SCHEMA='dbo' 
            AND COLUMN_NAME LIKE '%price'
        for xml path('')
    ), 1, 1, '')

set @sql = 'select
                *
            from
                mytable
                cross apply (select 
                                minVal = min(val) 
                            from (
                                values ' + @cols + '
                            ) t(val) where val > 0
                ) q'

exec (@sql)

Upvotes: 3

Eralper
Eralper

Reputation: 6612

You can create a dynamic SQL statement and execute it in the following form

declare @tablename sysname = 'MultipleNumericColumns'
declare @sql nvarchar(max) 

select @sql = isnull(@sql + ' union all ','') + '
SELECT ' + name + ' as colname from ' + @tablename  
from sys.all_columns 
where 
    object_id = OBJECT_ID(@tablename)

set @sql = '
select min(colname)
from (
' + @sql + '
) t
where colname > 0'

EXECUTE(@sql)

You can realize that first I get the column names from system view

You can exclude columns that you don't want or use a pattern like name like '%price% etc at this step

Then I build a dynamic SQL query into a string variable as sql command Please note that I use WHERE clause for greater than 0, etc

Final step is execution with EXECUTE command

Upvotes: 1

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Use UNPIVOT

SELECT Min(VALUE)
FROM (
    SELECT Col1, Col2, Col3...
    FROM YourTable

) t
UNPIVOT (VALUE FOR ITEM IN (Col1, Col2, Col3...)) u
WHERE VALUE != 0

Upvotes: 0

Related Questions