Remove from select only entries which repeat a certain value

Question

So, i have a very complicated database structure which I hope I won't have to detail. The main idea is that we have, lets say

Name WasHeHere

Robert yes
Michael yes
Robert no
Michael yes
John no
Robert yes

What I need is: primarily to sort a select (with multiple inner joins) after a certain value ('yes') of field A (WasHeHere) and then (and only then) remove any other rows which repeat a name.

The result should be:

Name WasHeHere

Robert yes
Michael yes
John no

Is there a MySql query that can do that?

And yes, I want to filter a select (JDeveloper, don't know why I cannot submit my answer to your question)

Answer 1

You can use DISTINCT for filtering unique record.

You can try this :-

select distinct(Name),WasHeHere,case WasHeHere when 'yes' then '1' else '0' end as pstatus from tablename order by pstatus desc;

Read more.

Hope it will help you.