CODEWITHSUNDEEP
sqloracle-database
Damien-Amen
Damien-Amen

Reputation: 7502

Extract row with a particular scenario

I have a table with following details

EMP_NAME     TEST_RESULT   RUN_DATE
----------------------------------------------
John            Pass    12-FEB-2013 18:03:55
Albert          Fail    12-FEB-2013 18:24:29
Chris           Pass    12-FEB-2013 18:24:28
John            Fail    12-FEB-2013 19:32:35
Chris           Pass    12-FEB-2013 19:32:35
Steve           Pass    12-FEB-2013 20:04:35

Now I need to extract row with unique names AND TEST_RESULT. But the test result should have only "Fail" result if employee has both pass and fail results. The result of the query should be

EMP_NAME     TEST_RESULT   RUN_DATE
----------------------------------------------
Albert          Fail    12-FEB-2013 18:24:29
Chris           Pass    12-FEB-2013 18:24:28
John            Fail    12-FEB-2013 19:32:35
Steve           Pass    12-FEB-2013 20:04:35

All I care about is only one EMPLOYEE NAME and if there are two results pass and fail for that EMPLOYEE, show me only FAIL record and I don't care about the time. If there are two results for an EMPLOYEE as "pass" and "pass" I still need one record and i don't care about the time.

Upvotes: 1

Views: 77

Answers (3)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

SELECT EMP_NAME, TEST_RESULT, RUN_DATE
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY t1.EMP_NAME ORDER BY RUN_DATE) AS RowNumber,
       t1.EMP_NAME, TEST_RESULT, RUN_DATE
  FROM MyTable t1
  LEFT JOIN 
  (SELECT DISTINCT EMP_NAME 
   FROM MyTable
   WHERE TEST_RESULT = 'Fail' 
  ) AS EMP_With_Failure ON t1.EMP_NAME = EMP_With_Failure.EMP_Name
  WHERE t1.TEST_RESULT = 'Fail' OR EMP_With_Failure.EMP_NAME IS NULL ) t
WHERE RowNumber = 1

This query will filter out any Success record for any user that have a failure, you still need to filter the result set to get only one record per user

Upvotes: 1

igr
igr

Reputation: 3499


Select emp_name, test_result, run_date
from (
select emp_name, test_result, run_date,
row_number() over (partition by emp_name
order by test_result, run_date ) rn
from
T1)
where rn=1

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

Something like this might work.

select emp_name, test_result, run_date
from YourTable
where test_result = 'Fail'
union
select emp_name, test_result, run_date
from YourTable
where test_result = 'Pass'
and emp_name in 
(select emp_name
from YourTable
where TestResult = 'Pass'
minus
select emp_name
from YourTable 
where TestResult = 'Fail'
)

Upvotes: 1

Related Questions