How to get distinct row based on criteria

I have table with data like this

<pre>
ID | Desc | Status
------+------+------------
1 | abc | Completed
1 | abc | Completed
1 | def | Planned
1 | def | Planned
1 | ghi | Rescheduled
1 | ghi | Rescheduled
2 | abc | Completed
2 | def | Planned
2 | ghi | Planned
</pre>

I need to get one row for each ID based on Status. One row for Planned and for other, if an ID has both status Completed & Rescheduled, then Rescheduled row must be selected or else Completed

e.g.

ID 1 has Planned, Completed & Rescheduled status. The output should be as below

one row Planned for ID=1 & one row "Rescheduled" for ID=1

<pre>
ID | Desc | Status
------+------+------------
1 | ghi | Rescheduled
1 | def | Planned
2 | abc | Completed
2 | def | Planned
</pre>

Your can do it with NOT EXISTS and then return (as your expected output) the minimum value of desc for each combination of id and status:

select t.id, min(t.[desc]) [desc], t.status 
from tablename t
where status in ('Planned', 'Rescheduled')
or not exists (
  select 1 from tablename
  where id = t.id and status = 'Rescheduled'
)
group by t.id, t.status
order by t.id, t.status

See the demo.<br/>
Results:

> id | desc | status     
> -: | :--- | :----------
>  1 | def  | Planned    
>  1 | ghi  | Rescheduled
>  2 | abc  | Completed  
>  2 | def  | Planned 

you can try this

select id, desc, status, 1 count from(
select id, desc, status, ROW_NUMBER() over (partition by id) rownum from(
select distinct id, desc, status, 1 count
from tablename where status = 'Planned')  x ) y
where rownum = 1
union all
select id, desc, status, 1 from(
select id, desc, status, ROW_NUMBER() over (partition by id order by status desc) rownum from(
select distinct id, desc, status from tablename where status in ('Rescheduled','Completed')) x ) y
where rownum = 1
order by 1,2,3

You can use CTE to remove duplicate records, then apply CASE WHEN THEN to get desired results like :

WITH CTE_Result AS
(
  select distinct Id,description, status 
  from tdata
)
select Id,
CASE 
WHEN description='Planned' THEN 'Planned'
WHEN description='Planned'OR description='Rescheduled' AND (select Count(t.description) from tdata t where t.id=id)>1 THEN 'Rescheduled'
ELSE 'Completed'
END as description,
status
from CTE_Result;

Sample fiddle is http://www.sqlfiddle.com/#!18/cb394/49

You can look at the minimum status per ID, which is either 'Completed' or not:

with data as
(
  select
    id, status, min([Desc]) as description, min(status) over (partition by id) as min_status
  from mytable
  group by id, status
)
select id, status, description
from data
where status in ('Planned', 'Completed') or min_status <> 'Completed'
order by id, status;

I figured out using logic from @Krishna Muppalla and @Thorsten Kettner. Thanks you all for your suggestions.

select * from 
(
	select 
		 distinct ID
		,Desc
		,Status
		,max(Status) over (partition by ID, desc) as New_Status
	from tdata 
	where Status in ('Completed','Rescheduled')
) A
where A.Status = A.New_Status

UNION

select 
	 distinct ID
	,Desc
	,Status
	,max(Status) over (partition by ID, desc) as New_Status
from tdata 
where Status in ('Planned')