CODEWITHSUNDEEP
sqloracle-database
archana k
archana k

Reputation: 27

Select single value from same columns based on condition

I have below table and using oracle sql

table name : TestCaseStatus

TestName  Status   TimeStamp
ABC       Passed   11.10AM (Same Date)
ABC       Failed   11.00 AM 
ABC       Failed   10.50 AM
EFG       Passed   11.00AM
123       Failed    11.10 AM
123       Passed    11.00 AM

Result 

TestName  Status
ABC       Passed_On_ReRun
123       Failed
EFG       Passed

Question : Need query to get it. I have Tried MAX but not working

Upvotes: 0

Views: 184

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Assuming these are the only three conditions, you can use conditional aggregation:

select testname,
       (case when max(timestamp) = max(case when status = 'Failed' then timestamp end)
             then 'Failed'
             when max(timestamp) = max(case when status = 'Passed' then timestamp end) and
                  sum(case when status = 'Failed' then 1 else 0 end) > 0
             then 'Passed_On_Rerun'
             when max(timestamp) = max(case when status = 'Passed' then timestamp end)
             then 'Passed'
             else '???'
         end)
from TestCaseStatus tcs
group by testname;

In Oracle, you can simplify this to:

select testname,
       (case when max(status) keep (dense_rank first order by timestamp desc) = 'Passed' and
                  sum(case when status = 'Failed' then 1 else 0 end) > 0
             then 'Passed_On_Rerun'
             else max(status) keep (dense_rank first order by timestamp desc)
         end)
from TestCaseStatus tcs
group by testname;

Here is a db<>fiddle.

The keep syntax is getting the last value for the status based on the timestamp.

Upvotes: 0

Gosfly
Gosfly

Reputation: 1300

You may achieve what you want with the following query :

SELECT T1.TestName, T1.Status 
FROM TestCaseStatus T1
WHERE T1.TimeStamp = 
     (SELECT MAX(T2.TimeStamp) FROM TestCaseStatus T2 WHERE T2.TestName = T1.TestName)

However, there is an issue with your sample data :

123       Failed    11.00 AM
123       Passed    11.00 AM

Same test is failed and passed for the same timestamp ?!

Edit : In order to achieve the 'Passed_On_ReRun' need, we could imagine a solution like this :

SELECT T1.TestName, IF(T1.count > 0 AND T1.Status = 'Passed', 'Passed_On_ReRun', T1.Status)
FROM 
    (SELECT T2.TestName
            , T2.Status
            , SUM(T2.Status = 'Failed') AS count
            , MAX(T2.TimeStamp) AS TimeStamp 
     FROM TestCaseStatus T2 
     GROUP BY T2.TestName) T1

Upvotes: 0

GMB
GMB

Reputation: 222402

You can do this with window functions:

select 
    TestName,
    case 
        when Status = 'Passed' and failed_once = 1 
        then 'Passed_On_Rerun'
        else Status
    end Status
from (
    select
        t.*,
        rank() over(partition by TestName order by Timestamp desc) rn,
        max(case when Status = 'Failed' then 1 else 0 end) over(partition by TestName) failed_once
    from TestCaseStatus t
) t
where rn = 1

Demo on DB Fiddle:

TESTNAME | STATUS         
:------- | :--------------
123      | Failed         
ABC      | Passed_On_Rerun
EFG      | Passed

Upvotes: 1

Related Questions