Ignoring records for certain criteria

Transact-SQL
1

I have data as below,

asdf 1
asdf 2
asdf 3
kjhj 1
qwer 1
qwer 1

need to get output:
kjhj 1
qwer 1

situation is that need to get records that have only "1" in 2nd column. If they have any other value other than "1", need to ignore all records for particular 1st column.
can you plz suggest a query

2

You can write a simple query, if you had provided more details I could have helped you more:

SELECT DISTINCT
Column1, Column2
FROM YourTable
WHERE Column2 <> "1";

4

Hi

Hope this helps

create data script

drop table if exists #Temp
create table #Temp ( col1 varchar(20) , col2 int )

insert into #Temp select 'asdf',1
insert into #Temp select 'asdf',2
insert into #Temp select 'asdf',3
insert into #Temp select 'kjhj',1
insert into #Temp select 'qwer',1
insert into #Temp select 'qwer',1

;WITH cte AS  
(
SELECT
    col1 
  , case when min(col2) = max(col2) then 1 end as col2
FROM
    #Temp
GROUP BY 
       col1
)
SELECT 
     * 
FROM 
    CTE 
WHERE 
    col2 is not null

image

1 Like
5

Another way to do it

Simpler

SELECT
    min(col1) 
  , min(col2)
FROM
   #Temp
GROUP BY 
      col1
HAVING 
     min(col2) = max(col2)

image

1 Like
6 

SELECT col1
FROM #Temp
GROUP BY col1
HAVING MAX(CASE WHEN col2 = 1 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN col2 <> 1 THEN 1 ELSE 0 END) = 0