sql server

Table
Location      Branch
19      0
19      0
21      0
21      0
22      0
22      0
22      101
22      101
 

Please help to write an sql from table 1 to get the result

Result needed
Location      Branch
19      0
19      0
21      0
21      0
22      101
22      101

Drop null branch values if branch has a non null value.
swpa_wntAsked:
Who is Participating?
 
Terry WoodsConnect With a Mentor IT GuruCommented:
Is this for MySQL or SQL Server? The question title and selected Topic Area don't match.

Are there Branch values that are actually null, as your data seems to show them as 0?

Anyway, this might work for you:

select *
from my_table as m
where Branch is not null
or not exists (select 1 from my_table where Location=m.Location and Branch is not null)

Open in new window

0
 
HuaMin ChenSystem AnalystCommented:
Try
select *
from tab1
where (location,branch) in(select location,max(branch)
from tab1
group by location)

Open in new window

0
 
swpa_wntAuthor Commented:
Thanks to both for our replies . Will try them and let you know. This is for Sql server.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
MartinChaddertonConnect With a Mentor Commented:
Slightly convoluted but:

Select location,branch
INTO #tmp
from <yourtable>

Delete from #tmp
where branch = 0 and location in (select location from #tmp where branch > 0)

Select location,branch
from #tmp
order by location
0
 
swpa_wntAuthor Commented:
I've requested that this question be deleted for the following reason:

Hi,

It appears our account was used to post this question, but we cannot locate the asker.  I do not know if the experts' suggestions worked, or if a different solution was found, or even if the question became moot.  It's also possible the asker is no longer here.  I apologize for the inconvenience.
0
 
Terry WoodsIT GuruCommented:
I think it's more appropriate to just accept a solution here.
0
 
Terry WoodsIT GuruCommented:
I recommend a split between http:#a39984883 and http:#a40137964

I'm reasonably sure the technique used in the other given solution (by HuaMinChen) would not work, as I don't think you can use IN with multiple columns. Someone please correct me if I'm wrong.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.