title: SQL Teradata query
body: <p>I have a table abc which have many records with columns col1,col2,col3,</p>
  
  <pre><code>dept         | name |  marks |
  
  science         abc      50
  science         cvv      21
  science         cvv      22  
  maths           def      60
  maths           abc      21
  maths           def      62
  maths           ddd      90
  </code></pre>
  
  <p>I need to order by dept and name with ranking as ddd- 1, cvv - 2, abc -3, else 4 then need to find out maximum mark of an individual. Expected result is   </p>
  
  <pre><code>dept         | name |  marks |
  
  science         cvv      22
  science         abc      50
  maths           ddd      90
  maths           abc      21
  maths           def      62
  </code></pre>
  
  <p>. How may I do it.?            </p>