AdventureWorks Database: Select data from first column that has a nonnull value

SQL Query - AdventureWorks: Exercise-130 with Solution

130. From the following table write a query in SQL to select the data from the first column that has a nonnull value. Retrun Name, Class, Color, ProductNumber, and FirstNotNull.

Sample table: production.Product

Click to view Full table

Sample Solution:

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) AS FirstNotNull  
FROM Production.Product;

Sample Output:

name                            |class|color       |productnumber|firstnotnull|
--------------------------------+-----+------------+-------------+------------+
Adjustable Race                 |     |            |AR-5381      |AR-5381     |
Bearing Ball                    |     |            |BA-8327      |BA-8327     |
BB Ball Bearing                 |     |            |BE-2349      |BE-2349     |
Headset Ball Bearings           |     |            |BE-2908      |BE-2908     |
Blade                           |     |            |BL-2036      |BL-2036     |
LL Crankarm                     |L    |Black       |CA-5965      |L           |
ML Crankarm                     |M    |Black       |CA-6738      |M           |
HL Crankarm                     |     |Black       |CA-7457      |Black       |
Chainring Bolts                 |     |Silver      |CB-2903      |Silver      |
Chainring Nut                   |     |Silver      |CN-6137      |Silver      |
Chainring                       |     |Black       |CR-7833      |Black       |
Crown Race                      |     |            |CR-9981      |CR-9981     |
Chain Stays                     |     |            |CS-2812      |CS-2812     |
Decal 1                         |     |            |DC-8732      |DC-8732     |
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Evaluate whether the values in two columns are the same.
Next: Check for similarity of the values.