开发者

Proper sql query in MySQL

I have table like this:

Name   Result  
T1     fail  
T2     pass  
T3     pass  
T2     fail  
T4     fail  
T1     pass  
T4     fail

开发者_运维问答Now, I want to get a results like this:

Name   Result
T1     pass
T2     pass
T3     pass
T4     fail

I tried using query like this, but it does not work.

select (case when Result = "pass" then "pass" else "fail" end) as Final_verdict,    
Name from table_1 group by Name

Can anyone please tell me what am I missing?


EDIT

Try the following:

SELECT Name, MAX(Result) As Result
FROM table_1
GROUP BY Name

This should return pass whenever there is one passed record for a row, as pass is lexicographically after fail.


Use else instead of the second then, and use end at the end of the case.
You might also have to use single quotes.

select (case when Result = 'pass' then 'pass' then 'fail' end) as Final_verdict,
       Name
from table_1

What do you expect to happen when the same name has different results?


Not the most pretty way, but depending on what you want to happen:

You want fail when there is any fail:

select name, min(result) from table_ 1 group by name;

You want pass if there is any pass:

select name, max(result) from table_ 1 group by name;

Or did you only want the last result for each Name in the table?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜