Need help to write one SQL query in Oracle

Question

I have a table named LOCATION. It has column like location_id, location_nme, pstl_cd, area_nme.

When am writing below queries giving two rows of output:

1st case:

Select * from Location where location_id= 123;

Output:
location_id    location_nme     pstl_cd        area_nme
123             Ramnagar         721423          Null
123             Ramnagar         721423          Southern

Here we need to pick only the values where area name is not null.

123             Ramnagar         721423          Southern

2nd case:

Select * from Location where location_id= 789;

location_id    location_nme     pstl_cd     area_nme
789            Balisai           700543          Null

Here area_name is coming as null. So if I add in query like where area_nme is not null, the 2nd case will not be satisfied.

Can you please help to write one query, where both the cases will be satisfied.

Answer 1

You want to return one row only. In case there are two candidates, you want to return the better row. There are various approaches to this. For one row, simply order your data such that you get the best row first and then use FETCH FIRST ROW ONLY to only keep that row:

select *
from locations
where location_id = :location_id
order by area_name nulls last
fetch first row only;

For more than one location, you can use a window function (ROW_NUMBER, RANK, DENSE_RANK) in order to rank your rows. As you are no longer returning one row then, you'll have to change your FETCH FIRST ROW to WITH TIES or re-structure the query. E.g.

select *
from locations
order by rank() over (partition by location_id order by area_name nulls last)
fetch first row with ties;