title: select top 10 students from database
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/2396073/sql-to-select-top-10-records">sql to select top 10 records</a>  </p>
  </blockquote>
  
  
  
  <p>Assuming you have one table with StuId, StuName, Subject, Grade. You are required to provide a subquery to return a list of honor students (top 10 percent), sorted by their grade point average.</p>
  
  <p>for example, if I have 10 student whose average grade is 100,90,80,...10. 
  It is required to output the first student name whose average is 100. Only one student grade is output. So I can't use limit 10</p>
  
  <p>I am using mysql 5.1.</p>
  
  <p>Here is my query: </p>
  
  <pre><code>SELECT Stuname, TOP 10 Avg(Grade) as GPA 
    FROM Table 
  GROUP BY Stuid 
  ORDER BY GPA
  </code></pre>
  
  <p>The query is not correct because of the <code>TOP 10</code>. Checking MYSQL 5.1 reference, it does not support top 10. </p>
  
  <p>According to comments, I dont think it can be resolved using one query, so I come up with a method:</p>
  
  <pre><code>F(conn){
  Statement stmt;
  int top10percnet = 0;
  try{
  stmt = conn.createStatement;
  String query = "select CEIL(count(stuname)*10/100) as 10percent from grades";
  ResultSet rs = stmt.execute(query);
  while(rs.next()){
    top10percent = rs.getString("10percent");
  }
  query = "select stuname, avg(grade) as average from grades group by stuname order
  by average desc limit " + Integer.toString(top10percnet);
  rs=stmt.execute(query);
  while(rs.next()){
     ...// output result
  }
  }
  catch(SQLException e){
  }
  
  
  
  }  
  </code></pre>