Select records with multiple unique columns, choosing non-null over another priority column

Question

I would like to query the table by getting unique entries in field1 and field2 when selecting a row with field4 so that it takes precedence over some value, not zero.

eg. in the original table

field1 field2 field3 field4 1 BA (null) 1 BA 2 2 CA 3 2 DA (null) 3 DF 3 2 CA 3 

And what I want to get from the output request is as follows:

 field1 field2 field3 field4 1 BA 2 2 CA 3 2 DA (null) 3 DF 3 

Is there any efficient way to use SQL join / filtering technology for this? Thanks

P / S - to avoid confusion, the goal is to distinguish only field1 and field2 is only that field 3 can have different values, so that the selected row is based on that row in which field4 does not have zero priority

eg.

 field1 field2 field3 field4 1 BA (null) 1 BC 2 <- this will be the chosen one 

Answer 1

try the following:

You just need to group by the first 3 fields and take MAX () of filed4

 select "field1","field2","field3",max("field4") from Table1 group by "field1","field2","field3" order by "field1" 

SQL script demo

EDIT:

If you want only field1 and field2 as part of the grouping, try this:

 select "field1","field2","field3","field4" from( select "field1","field2","field3","field4", row_number() over(partition by "field1","field2" order by "field4" desc) as rn from Table1)A where A.rn=1 

SQL Fiddle Demo 2

Answer 2

You can use max, but you must be sure that you select the correct field3. This will do the following:

 select field1, field2 , max(field3) keep (dense_rank first order by field4 desc) as field3, max(field4) from Table1 group by field1, field2 ,field3, order by field1, field2