DISTINCT but not really-CodePudding

I have table similar to the following:

data1 | data2 | data3 | data4 | important data
______________________________________________
res1  | res1  | res1  | res1  | NULL         
res2  | res2  | res2  | res2  | NULL         
res2  | res2  | res2  | res2  | wow         
res3  | res3  | res3  | res3  | NULL         
res4  | res4  | res4  | res4  | wow

I'd like it to return something like this:

data1 | data2 | data3 | data4 | important data
______________________________________________
res1  | res1  | res1  | res1  | NULL               
res2  | res2  | res2  | res2  | wow         
res3  | res3  | res3  | res3  | NULL         
res4  | res4  | res4  | res4  | wow

so choose distinct(data1, data2, data3, data4) but if such quadruple exist for both NULL and wow, choose always wow. Is there a way to specify which rows does DISTINCT eliminate?

CodePudding user response：

You can use aggregation with max() (or min()) here, since NULLs aren't greater than, nor equal, nor less than any other value.

SELECT data1,
       data2,
       data3,
       data4,
       max(importantdata) AS importantdata
       FROM elbat
       GROUP BY data1,
                data2,
                data3,
                data4;