title: compare records within same table
body: <p>I have  Student_Table with following structure.</p>
  
  <pre><code> Student_Note  Table 
    student_id  seq_num  note 
    11212         1       firstnote
    11212         2       secondNote 
    11212         3       thirdNote
    21232         1       secondstudentnote1
    21232         2       secondstudentnote2
  </code></pre>
  
  <p>so on </p>
  
  <p>I want   to get latest note (which has largest seq_num)   for  a particilar student.</p>
  
  <p>I have tried following query</p>
  
  <pre><code>  select tn.note from Student_Note tn   JOIN Student_Note tn1
              ON (tn.student_id =tn1.student_id AND  tn.seq_num &lt; tn1.seq_num)
               where tn.student_id=11212
  </code></pre>
  
  <p>it is giving more than one row. How to achieve aforementioned scenario ?</p>
  
  <p>I forgot mention  that I am using above  query as subquery . As per sybase  TOP clause will not work in subquery.</p>
  
  <p>P.S. I am using sybase.</p>