CODEWITHSUNDEEP
sqlsql-servert-sql
biviz
biviz

Reputation: 173

SQL: return group of rows where condition is true

My dataset looks like this:

PNR    Action Code
-----+-------------
1    |  Book
1    |  Exchange
1    |  Cancel
2    |  Book
2    |  Exchange
3    |  Book
4    |  Book
4    |  Cancel

I am trying to return all the rows for PNRs where Action code was "Cancel". So in this case, all the rows for PNRs 1 & 4.

I tried to use a Where Clause but that won't be correct as it would return only the rows WHERE action code was "Cancel". My result should look like:

PNR    Action Code
-----+-------------
1    |  Book
1    |  Exchange
1    |  Cancel
4    |  Book
4    |  Cancel

Upvotes: 2

Views: 869

Answers (7)

dybzon
dybzon

Reputation: 1594

I would write it like this, using a CTE to make it easily readable:

WITH RelevantPNRs AS(
    SELECT DISTINCT PNR
    FROM [YourTable]
    WHERE [ActionCode] = 'Cancel'
)
SELECT t.*
FROM [YourTable] t
    INNER JOIN RelevantPNRs r ON t.PNR = r.PNR

Upvotes: 0

Eli
Eli

Reputation: 2608

You can take the CTE route here as well.

; with cte as (
    select 
    pnr
    ,[action code]
    ,SUM(case
        when [action code] = 'cancel' then 1
        else 0
    end ) OVER(partition by pnr order by pnr) as canceledItem
from test
)
select *
from cte
where canceledItem = 1

Upvotes: 0

Javlon Ismatov
Javlon Ismatov

Reputation: 194

select * 
from your_table 
where PNR=1 or PNR=4

Upvotes: -1

freshp
freshp

Reputation: 21

I am not sure if i get your answer but i think this should help you

select * 
from your_table 
where PNR in (select PNR 
              from your_table 
              where Action_Code like 'Cancel')

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can query as below:

Select * from (
    Select *, SmCnt = Sum(case when [Action Code] = 'Cancel' then 1 else 0 end) over(partition by pnr) 
    from #pnrdata
) a where a.SmCnt > 0

Output as below:

+-----+-------------+
| PNR | Action Code |
+-----+-------------+
|   1 | Book        |
|   1 | Exchange    |
|   1 | Cancel      |
|   4 | Book        |
|   4 | Cancel      |
+-----+-------------+

Upvotes: 1

RMH
RMH

Reputation: 222

I think you need to do a self INNER JOIN in your table. Something like that:

select      b.*
from        yourtable a
inner join  yourtable b
on          a.PNR = b.PNR
where       a.Action_Code = 'Cancel'

Upvotes: 2

Degan
Degan

Reputation: 989

Perhaps get the PNRs that you want from a select statement in your Where clause?

Select T1.PNR
      ,T1.[Action Code]
From myTable T1
Where T1.PNR in (
       Select PNR
       from myTable
       where [Action Code] = 'Cancel'
       )

Upvotes: 8

Related Questions