Selecting an actual MIN value instead of NULL in SQL query

user167908

From this table:

Select * into #tmp from (
select 'a' A, 'b' B, NULL C union all
select 'aa' A, 'ab' B, 1 C union all
select 'aaa' A, 'bbb' B, 2 C ) x

I'd like to get this result:

A   B   Val
a   b   1
aa  ab  1
aaa bbb 2

That is, take the non-null min value and replace the NULL.

I suppose I could join the table to a filtered version of itself where no nulls appear. But that seems overkill. I thought this might be able to be done in the MIN Aggregate clause itself.

Any ideas would be helpful, thanks!

user2062108

declare @null int

select @null = MIN(c) from #tmp

select A, B, ISNULL(c,@null) as val1 from #tmp

or

select A, B, ISNULL(c,(select MIN(c) from #tmp)) as val1 from #tmp

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Selecting query sql with % as value character

SQL - selecting min value and returning all column information for that min

JSON_VALUE value returning NULL instead of actual value

Selecting a Max and Min from the same column in SQL in a single query

Selecting MIN and MAX for a collection, based on an ActiveRecord scope, in one SQL query

How to have a NULL instead of No Value from a query

Selecting row by min value and merging

SQL query for selecting value with DISTINCT and only one value posted lastly

SQL Server : selecting min value and returning all column information for that min without using CTE

SQL Join query returns null instead of names

Sql: Query returns column name instead of Value

sql query count rows per id to by selecting range between 2 min dates in different columns

Selecting max and min dates in a group by with null values

MVC 5 - Why is my linq code selecting an entire query instead of a single value?

SQL Select Min By Group and Null when Value does not exist

Selecting the min num for each value of a column

SQL Query, MIN isn't returning just the lowest value

SQL Query only to return min/Max Value for each row

Group by MIN value in SQL Server pivot table query

Get min and max value from database Linq to Sql query in MVC?

Default value for some key in MySQL query's result instead of `null`

Behavior of TOP with WHERE clause . Why is this query returning a value instead of NULL?

SQL Query GROUP BY MIN?

Simple SQL query returning null value

Set value to NULL from select query in SQL

Null value in column resulting into no output in sql query

SQL Query returns unexpected NULL value

Axios method is passing item as null, instead of the actual value to my mongoose route

How to show "0" as default instead of "Null" in sql select query