I would like to query a table by getting records unique to field1 and field2 while selecting the row with field4 to have some value rather than null in priority.
e.g. in original table
field1 field2 field3 field4
1 B A (null)
1 B A 2
2 C A 3
2 D A (null)
3 D F 3
2 C A 3
And what I wish to get from output query is like:
field1 field2 field3 field4
1 B A 2
2 C A 3
2 D A (null)
3 D F 3
Is there any efficient way of SQL table joinning technique / filtering to achieve this? Thanks
P/S - to avoid confusion, the purpose of having distinct of field1 and field2 only is because field3 can be having different values that the chosen row will be based on those row which field4 is not null in priority
e.g.
field1 field2 field3 field4
1 B A (null)
1 B C 2 <- this will be the chosen one
try this:
you Just need to group by the first 3 fields and take the MAX() of filed4
select "field1","field2","field3",max("field4")
from Table1
group by "field1","field2","field3"
order by "field1"
EDIT:
If you want only field1 and field2 as part of grouping then 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
You may use max, but must be sure that you choose the right field3. This will do that:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With