Solved

# Filter results in select

Posted on 2011-03-14
298 Views
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

0
Question by:PeteEngineer

LVL 3

Accepted Solution

CarlsbergFTW earned 167 total points
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

CarlsbergFTW earned 167 total points
select * from table where upper(name1) like upper('% o')
0

LVL 39

Assisted Solution

Pratima Pharande earned 83 total points
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

LVL 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 83 total points
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
``````
0

LVL 8

Author Comment

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

mkobrin earned 83 total points
0

LVL 29

Assisted Solution

Olaf Doschke earned 84 total points
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')
``````

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

### Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.