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.

What do you need exactly? Do you need all Locations in output or only this two IDs? — TimLer
– TimLer, Commented Aug 23, 2021 at 5:31
There will be many duplicate location_id, I need only distinct location_id — nodev_101
– nodev_101, Commented Aug 23, 2021 at 5:49

Thorsten Kettner · Accepted Answer · 2021-08-23 05:42:57Z

2

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;

edited Aug 23, 2021 at 5:42

answered Aug 23, 2021 at 5:37

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need help to write one SQL query in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related