Skip to main content
Database Administrators

Return to Answer

Added SQLFiddle link.
Source Link
RDFozz
RDFozz
  • 11.7k
  • 4
  • 24
  • 38

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Here's a SQLFiddle showing it in action.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

Here's a SQLFiddle (against SQL Server - the basic command should work in just about any SQL language) showing it in action. You can see that it picks values from different rows if needed. Even if only one out of five rows has a non-NULL value, both MAX and MIN will return that one non-NULL value. You only get NULL if there are no non-NULL values at all for a given UnitID.

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Here's a SQLFiddle showing it in action.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

Here's a SQLFiddle (against SQL Server - the basic command should work in just about any SQL language) showing it in action. You can see that it picks values from different rows if needed. Even if only one out of five rows has a non-NULL value, both MAX and MIN will return that one non-NULL value. You only get NULL if there are no non-NULL values at all for a given UnitID.

Added SQLFiddle link.
Source Link
RDFozz
RDFozz
  • 11.7k
  • 4
  • 24
  • 38

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Here's a SQLFiddle showing it in action.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Here's a SQLFiddle showing it in action.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

Source Link
RDFozz
RDFozz
  • 11.7k
  • 4
  • 24
  • 38

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.