title: Displaying staffname,subject name and max marks scored in a table
body: <p>This is the question statement the code should be in oracle..
  Write a query to display list of staff name, subject name handled, maximum mark scored in that subject. Give an alias to the maximum mark as max_mark. Sort the result based on maximum mark in descending.
  <a href="https://i.stack.imgur.com/4x9UV.jpg" rel="nofollow noreferrer">This is the schema for the tables </a></p>
  
  <p>I tried to use join to tackle this problem using this code.... but the results i get are weird.. please help as to get the proper result.</p>
  
  <pre><code>select staff_name,subject_name,value as max_mark
  from subject
  join staff using(staff_id)
  join mark using(subject_id)
  where(staff_id,value)
  in(select staff_id,max(value)
  from subject group by staff_id)
  order by max_mark desc;  
  </code></pre>
  
  <p>output i get is </p>
  
  <p><a href="https://i.stack.imgur.com/fpmSb.png" rel="nofollow noreferrer">output-part1</a>
  <a href="https://i.stack.imgur.com/9IULJ.png" rel="nofollow noreferrer">output-part2</a></p>
  
  <p>expected output is
  <a href="https://i.stack.imgur.com/6l5IP.png" rel="nofollow noreferrer">expected output-photo</a></p>