return lowest value from 4 fields 1

[blar9]

Col1 Col2 Col3 Col4
NULL 2 1 4
9 10 9 8
7 7 22 NULL

If I have a table like the one above how would I return the lowest value
of any row that is not null as some alias? Above statement should return
Alias
1
8
7

Thanks for any help

 

[gmmastros]

Do you have any other columns in this table? If you had a primary key in this table, then you could use union all with a min aggregate. If there is a primary key column, let me know and I will show you a query that you can use to get the data you want.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blar9]

Yes there is a primary key column numeric field

 

[gmmastros]

Good.

In this example, I am creating a table variable to store your test data. You can copy/paste this to query analyzer to see how it works. If you are satisfied, then siply change the query to use your table/columns instead.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color] (Id [COLOR=blue]int[/color] [COLOR=blue]Primary[/color] [COLOR=blue]Key[/color], Col1 [COLOR=blue]Int[/color], Col2 [COLOR=blue]Int[/color], Col3 [COLOR=blue]Int[/color], Col4 [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1,NULL,     2 ,       1 ,      4)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, 9  ,     10,       9 ,      8)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3, 7  ,     7 ,       22,      NULL)

[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]Min[/color](Col) [COLOR=blue]As[/color] [COLOR=blue]MinValue[/color]
[COLOR=blue]From[/color]   (
		[COLOR=blue]Select[/color] Id, Col1 [COLOR=blue]As[/color] Col
		[COLOR=blue]From[/color]   @Temp
		
		Union All 
		
		[COLOR=blue]Select[/color] Id, Col2
		[COLOR=blue]From[/color]   @Temp
		
		Union All 
		
		[COLOR=blue]Select[/color] Id, Col3
		[COLOR=blue]From[/color]   @Temp
		
		Union All 
		
		[COLOR=blue]Select[/color] Id, Col4
		[COLOR=blue]From[/color]   @Temp
		) [COLOR=blue]As[/color] A
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blar9]

Declare @Temp Table (Id int Primary Key, Col1 Int, Col2 Int, Col3 Int, Col4 Int)

Insert Into @Temp Values(1,5, 7, NULL , NULL)
Insert Into @Temp Values(2, 9, 10, 9, 8)
Insert Into @Temp Values(3, 7, 7, 22, NULL)

Select Id, Min(Col) As MinValue
From (
Select Id, Col1 As Col
From @Temp

Union All

Select Id, Col2
From @Temp

Union All

Select Id, Col3
From @Temp

Union All

Select Id, Col4
From @Temp
) As A
Group By Id

This is perfect! Is there anyway to make this return the last value entered?
For example if each column represented a step I would like to have the query return
7,8,22 which is the last value that is not null.

 

[gmmastros]

The last value that is not NULL. That's even easier!

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color] (Id [COLOR=blue]int[/color] [COLOR=blue]Primary[/color] [COLOR=blue]Key[/color], Col1 [COLOR=blue]Int[/color], Col2 [COLOR=blue]Int[/color], Col3 [COLOR=blue]Int[/color], Col4 [COLOR=blue]Int[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1,5, 7,  NULL , NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, 9,  10, 9,  8)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3, 7, 7, 22, NULL)

[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]Coalesce[/color](Col4, Col3, col2, col1) [COLOR=blue]As[/color] [LastColValue]
[COLOR=blue]From[/color]   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[blar9]

Declare @Temp Table (Id int Primary Key, Col1 VarChar(50), Col2 VarChar(50), Col3 VarChar(50), Col4 VarChar(50))

Insert Into @Temp Values(1,'5','7', NULL, NULL)
Insert Into @Temp Values(2, '9', '10', '9', 'HERE')
Insert Into @Temp Values(3, '7', '7', '22', '')

Select Id, Coalesce(Col4, Col3, col2, col1) As [LastColValue]
From @Temp

Can this return '7','HERE','22' so it ignores empty strings and thanks alot
for all your help! I will make sure to give you some start : D.

 

[gmmastros]

Use a NullIf around each column, like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color] (Id [COLOR=blue]int[/color] [COLOR=blue]Primary[/color] [COLOR=blue]Key[/color], Col1 [COLOR=blue]Int[/color], Col2 [COLOR=blue]Int[/color], Col3 [COLOR=blue]Int[/color], Col4 [COLOR=blue]Int[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1,5, 7,  NULL , NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, 9,  10, 9,  8)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3, 7, 7, 22, NULL)

[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]Coalesce[/color]([COLOR=#FF00FF]NullIf[/color](Col4, [COLOR=red]''[/color]), [COLOR=#FF00FF]NullIf[/color](Col3, [COLOR=red]''[/color]), [COLOR=#FF00FF]NullIf[/color](col2, [COLOR=red]''[/color]), [COLOR=#FF00FF]NullIf[/color](col1, [COLOR=red]''[/color])) [COLOR=blue]As[/color] [LastColValue]
[COLOR=blue]From[/color]   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom