How to select and transfer specific string values into new column in SQL?

user_13702242 0
Viewed 115

I have a table of items that can be arranged into groups. The table contains an attribute column with string values, as an example I chose color. For some items, the color column is empty.

ItemID Group Color
001 A 'blue'
002 A 'blue'
003 A 'blue'
004 A 'blue'
005 A
101 B 'red'
102 B 'red'
103 B
104 B
105 B 'green'

From this table I want to select only the items that do not have a color assigned yet and display a proposed color in a new column. The proposed color should be taken from the item with the highest ID value within each group.

The desired output should look like this:

ItemID Group Proposed Color
005 A 'blue'
103 B 'green'
104 B 'green'

I have no idea how to select specific values from the color column based on values from another column (in this case ItemID) and assign them to a different row within my table. Any help (also keywords describing the concept associated with my problem...) would be greatly appreciated!

(edit) This is what I've tried so far:

WITH temp_id AS (
SELECT max(ItemID), GroupName
FROM myTable
WHERE Color IS NOT NULL
GROUP BY GroupName
)
SELECT myTable.*, temp_id.*
FROM myTable
JOIN temp_id
ON temp_id.groupName = myTable.groupName;

But the output is missing the color column and I wasn't able to add a condition WHERE Color IS NOT NULL to my outer selection.

sql
4 Answers

You have to join again with myTable in order to get the Color of the MaxID you calculated in the temp_id table:

WITH temp_id AS (
SELECT max(ItemID) AS MaxId, GroupName
FROM myTable
WHERE Color IS NOT NULL
GROUP BY GroupName
)
SELECT myTable.*, T2.Color
FROM myTable
JOIN temp_id
ON temp_id.groupName = myTable.groupName
JOIN myTable T2
ON T2.ItemID = temp_id.MaxID;

Fiddle here.

user_98051900

This could work as well:

WITH test_data AS
(
  SELECT '001' AS "item_id", 'A' AS "group", 'blue' AS "color" FROM DUAL UNION ALL
  SELECT '002', 'A', 'blue'  FROM DUAL UNION ALL
  SELECT '003', 'A', 'blue'  FROM DUAL UNION ALL
  SELECT '004', 'A', 'blue'  FROM DUAL UNION ALL
  SELECT '005', 'A', NULL    FROM DUAL UNION ALL
  SELECT '101', 'B', 'red'   FROM DUAL UNION ALL
  SELECT '102', 'B', 'red'   FROM DUAL UNION ALL
  SELECT '103', 'B', NULL    FROM DUAL UNION ALL
  SELECT '104', 'B', NULL    FROM DUAL UNION ALL
  SELECT '105', 'B', 'green' FROM DUAL 
)
SELECT items."item_id", items."group", colors."proposed color"
  FROM (SELECT td."group", MIN(td."color") AS "proposed color"
          FROM test_data td
         GROUP BY td."group") colors
         INNER JOIN (SELECT td."item_id", td."group"
                       FROM test_data td
                      WHERE td."color" IS NULL) items
            ON items."group" = colors."group"
 ORDER BY items."item_id";
user_48918150

As you're close, I'll give you the answer, feel free to ask if you don't understand :

SQL Fiddle

Query 1:

-- full query 
SELECT mt.itemID, mt.Group, tColor.Color as proposedColor
FROM myTable as mt
LEFT JOIN (
  SELECT tMax.Group, tC.Color
  FROM (
    SELECT max(t.ItemID) ItemID, t.Group
    FROM myTable as t
    WHERE t.Color IS NOT NULL
    GROUP BY t.Group
    ) tMax
  LEFT JOIN myTable tC
    ON tC.ItemID = tMax.ItemID
  ) tColor
  ON tColor.Group = mt.Group
WHERE mt.Color IS NULL

Results:

| itemID | Group | proposedColor |
|--------|-------|---------------|
|      5 |     A |          blue |
|    103 |     B |         green |
|    104 |     B |         green |

Query 2:

-- details :
-- 1) the get max id per group 

SELECT max(t.ItemID) ItemID, t.Group
FROM myTable as t
WHERE t.Color IS NOT NULL
GROUP BY t.Group

Results:

| ItemID | Group |
|--------|-------|
|      4 |     A |
|    105 |     B |

Query 3:

-- 2) the color per group
SELECT tMax.Group, tC.Color
  FROM (
    SELECT max(t.ItemID) ItemID, t.Group
    FROM myTable as t
    WHERE t.Color IS NOT NULL
    GROUP BY t.Group
    ) tMax
  LEFT JOIN myTable tC
    ON tC.ItemID = tMax.ItemID

Results:

| Group | Color |
|-------|-------|
|     A |  blue |
|     B | green |

Query 4:

-- 3) the row that need a proposed color
SELECT mt.itemID, mt.Group, null as proposedColor
FROM myTable as mt
-- LEFT JOIN...
WHERE mt.Color IS NULL

Results:

| itemID | Group | proposedColor |
|--------|-------|---------------|
|      5 |     A |        (null) |
|    103 |     B |        (null) |
|    104 |     B |        (null) |
user_55462670

You can use a MAX window function to retrieve the value of "Color" with respect to each ItemID, then get the rows that had "Color" assigned null:

SELECT ItemID,
       Group_,
       MaxColor AS Color
FROM (SELECT *,
             MAX(Color) OVER(
                 PARTITION BY Group_ 
                 ORDER     BY CAST(ItemID AS UNSIGNED)) AS MaxColor
      FROM tab) cte
WHERE Color IS NULL

Check the demo here.

Note: If you don't care about selecting only those specific rows with NULL values, you can just use the window function as follows.

SELECT ItemID,
       Group,
       COALESCE(Color, MAX(Color) OVER(
                           PARTITION BY Group
                           ORDER     BY CAST(ItemID AS UNSIGNED))) AS Color
FROM tab

The COALESCE function will give priority to the Color field, and when it is null, it will catch the MAX window function output.

user_124928900
Related