can someone please help me with the below requirement?

I have a table with sample data below

Sample Table

Now, I want to write a query that 1. It don't miss any ID in the report 2. If a particular ID has a Completed status then that will be included and other rows for same ID will be excluded 3. If no completed status then one Row with Faulted status will be included.

so below should be in my report


  • 1
    Guessing SQL Server, please clarify the rdbms. Also you need to convert those grids to text. Screenshots aren't welcome. – Evan Carroll Dec 5 '17 at 2:03
  • 1
    Voting to close as unclear, until the OP provides us with the DBMS. – ypercubeᵀᴹ Dec 5 '17 at 11:06

Assuming those are the only 2 status, or that any other status will come after 'completed' alphanumerically, you can use the min() aggregate function.

create table #test ([id] int, [status] varchar(50) )

insert into #test VALUES (5980681, 'COMPLETED')
insert into #test VALUES (5980681, 'FAULTED')
insert into #test VALUES (6890420, 'COMPLETED')
insert into #test VALUES (6890420, 'FAULTED')
insert into #test VALUES (6890421, 'FAULTED')

select id, min(status) 
from #test 
group by id

This is not a fool-proof solution for every situation, but will work for what you have described.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.