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?
精彩评论