Solved

Filter results in select

Posted on 2011-03-14
7
299 Views
Last Modified: 2012-05-11
select * from table

name1  4 o
name1  5 x
name1  3 x
name2  3 x
name3  2 x

I have above results in that name1 have values 3,4 and 5
I want to get the result as below where where the flag 'O' has been taken in case of multiple values for same name.here it is name1

name1  4 o
name2  3 x
name3  2 x

Please help!
0
Comment
Question by:PeteEngineer
7 Comments
 
LVL 3

Accepted Solution

by:
CarlsbergFTW earned 167 total points
ID: 35128002
select * from table where upper(name1) like ('% o')

The data you provided is consisted in 1 or more tables ?

please post more details if this is not what you're looking for.
0
 
LVL 3

Assisted Solution

by:CarlsbergFTW
CarlsbergFTW earned 167 total points
ID: 35128024
select * from table where upper(name1) like upper('% o')
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 83 total points
ID: 35128033
col1 change it to your name of name column and col3 is 'o' and 'x'

try this

select * from tablename
where col1 in (select col1 from tablename
Group by count(*)>1)
and col3 = 'o'
Union
select * from tablename
where col1 in (select col1 from tablename
Group by count(*)= 1)
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 83 total points
ID: 35128036
this might do:
select t.*
  from (select name_field, number_field, flag_field
             , row_number() over (partition by name_field order by case when flag_field = 'o' then 0 else 1 end) rn
          from yourtable 
        ) t
where t.rn = 1

Open in new window

0
 
LVL 8

Author Comment

by:PeteEngineer
ID: 35128037
no only one table... name1 is a value not a coulmn name  and o is a value in another column
0
 
LVL 7

Assisted Solution

by:mkobrin
mkobrin earned 83 total points
ID: 35128070
please supplt the column names and then we can help you with the query
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 84 total points
ID: 35128089
I think you're having three columns. But see how hard that is to tell, as CarlsbergFTW thinks of one column containing the whole lines?

If col1 contains name1,name2, etc and col2 containes the value 3,4,5 etc and col3 contains either 'x' or 'o', then it could be:

Select t1.* from table t1 where col3='o'
union
Select t1.* from table t1 where not exists (select 1 from table t2 where t1.col1=t2.col1 and t2.col3='o')

Open in new window


It smells like you need something more thoug, as your final result also has distinct values of col1 you might want to group by col1 and pick one record per group. While my solution should work (untested), it would fail, if you had more records for eg name2 with x but another int value, eg name2,2,'x' additionally to name2,3,'x' would also come into the final result.

You need to be more specific in what you want, then.

Bye, Olaf.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
Writing SQL Select Query result to a text file 12 32
SQL server is using more virtual memory. 5 71
TSQL previous 5 26
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now