Reputation: 95
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
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