w3resource

AdventureWorks Database: Find the data from the first column that has a non-null value

SQL Query - AdventureWorks: Exercise-187 with Solution

187. From the following table write a query in SQL to find the data from the first column that has a non-null value. Return name, color, productnumber, and firstnotnull column.

Sample table: Production.Product


Click to view Full table

Sample Solution:

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

Sample Output:

name                            |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                     |Black       |CA-5965      |Black       |
ML Crankarm                     |Black       |CA-6738      |Black       |
HL Crankarm                     |Black       |CA-7457      |Black       |
Chainring Bolts                 |Silver      |CB-2903      |Silver      |
Chainring Nut                   |Silver      |CN-6137      |Silver      |
Chainring                       |Black       |CR-7833      |Black       |
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Find all products that have NULL in the weight column
Next: Return rows only when two values in the two tables match.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv