Keevan
Keevan

Reputation: 95

Select one row for each main identifier where there are multiple rows for each id with null and non-null values

I have a query with rows that return results as shown here:

+--------------+------+-------+-------+------+------+--------+--------+--------+------+------+------+------+------+------+
|ID            | LC1  | IL1   |  IL2  | WT1  |  WT2 |   HN1  |  HN2   |   St   | P/H  | PN1  |  PN2 |  C1  |  C2  |  PW  |
+--------------+------+-------+-------+------+------+--------+--------+--------+------+------+------+------+------+------+
|NDP-5-SC-0014 | NULL | NULL  | 24.52 | NULL | 12.7 |  NULL  | 521502 |  NULL  | NULL | NULL | 509  | FBE  | NULL | NULL |
|NDP-5-SC-0014 | NULL | 18.07 | 24.52 | 12.7 | 12.7 | 402772 | 521502 | 341615 | NULL | 380  | 509  | FBE  | FBE  | NULL |
|NDP-5-SC-0014 | NULL | 18.08 |  NULL | 12.7 | NULL | 402772 |  NULL  |  NULL  | NULL | 380  | NULL | NULL | FBE  | NULL |

and I want to select it so that it shows as:

+--------------+------+-------+-------+------+------+--------+--------+--------+------+------+------+------+------+------+    
|ID            | LC1  | IL1   |  IL2  | WT1  |  WT2 |   HN1  |  HN2   |   St   | P/H  | PN1  |  PN2 |  C1  |  C2  |  PW  |
+--------------+------+-------+-------+------+------+--------+--------+--------+------+------+------+------+------+------+
|NDP-5-SC-0014 | NULL | 18.07 | 24.52 | 12.7 | 12.7 | 402772 | 521502 | 341615 | NULL | 380  | 509  | FBE  | FBE  | NULL |

I want to select one row for each ID and grab just one of the non-null values for the rest of the fields.

Upvotes: 0

Views: 32

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Conceptually.. You want to use an aggregate like MAX() or MIN() (whichever best fits your needs) on all of the columns except ID, which you will GROUP BY.

Without seeing your query it is impossible to give you the correct one back, but it will be something like:

SELECT ID, MAX(LC1), MAX(IL1), rest of columns
FROM (Your Query) A --May or may not need to put your query in a derived table
GROUP BY ID

Upvotes: 3

Related Questions