Remove null duplicates in Table

Question

My table looks like this:

rank   cost    segment      description
 1       23      pen         2mm red pen
 2       22      pen         3mm red pen
 3       10      pen         4mm green pen
 3               pen                       
 4               pen         5mm red pen
 5               pen         1 mm yellow pen
 1       3      eraser       red eraser
 2       2      eraser       red eraser
 3       5      eraser       green eraser
 4              eraser
 4       6      eraser       red eraser
 5              eraser       yellow eraser

Need to convert into:

 rank   cost    segment         description
     1       23      pen         2mm red pen
     2       22      pen         3mm red pen
     3       10      pen         4mm green pen
     4               pen         5mm red pen
     5               pen         1 mm yellow pen
     1       3      eraser        red eraser       
     2       2      eraser        red eraser
     3       5      eraser        green eraser
     4       6      eraser        red eraser
     5              eraser        yellow eraser

Note: Empty rank 3 from segment pen and rank 4 from segment eraser gets removed.

I have been trying to achieve this using select * from table where cost!=0 but in this case rank 5 gets removed as well because it is empty. Which is not desired.

I want to keep all the ranks even if its empty in each segment, just want to remove that duplicate rank in a segment which has empty cost value.

I tried several other ways to achieve this but failed. Is there any better way to achieve this? May be I need to use group by segment or any duplicate keyword, if there is any?

Apologies for such a novice question.

Any help will be highly appreciated.

Answer 1

Aggregation seems to do what you want:

select rank, sum(cost) as cost, segment, max(description)
from t
group by rank, segment;

This returns one row per segment/rank combination in the data.