aspose file tools*
The moose likes JDBC and the fly likes SQL query for top ten students Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query for top ten students" Watch "SQL query for top ten students" New topic
Author

SQL query for top ten students

faisal khan
Ranch Hand

Joined: Mar 13, 2002
Posts: 47
Hello
I have table name Student(name,marks,....)
I want to write the query that fetch the TOP TEN Students from the Student Table on the basis of their marks
regards
Faisal
[ March 18, 2004: Message edited by: Bear Bibeault ]
David Peterson
author
Ranch Hand

Joined: Oct 14, 2001
Posts: 154
You could use a SELECT statement with an ORDER BY clause and then pull off just the first ten rows using a for loop. Alternatively some databases allow you to specify the number of rows to return, as part of the query (e.g. the syntax in SQL Server is SELECT TOP 10 .... In MySQL it comes as the end of the query as ... LIMIT 10). Check out the documentation for your database.
faisal khan
Ranch Hand

Joined: Mar 13, 2002
Posts: 47
thanks for reply
but i want sql query for this in oracle
please send me the code
regards
Faisal :roll:
Tom Blough
Ranch Hand

Joined: Jul 31, 2003
Posts: 263
For Oracle, try:

Tom Blough


Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>
faisal khan
Ranch Hand

Joined: Mar 13, 2002
Posts: 47
thanks for sending query but it do not give the results that i want..
please send me the correct query for getting Top 10 students according
their (marks)
Faisal
Amy Phillips
Ranch Hand

Joined: Apr 02, 2003
Posts: 280
Hi Faisal,
In oracle you can use Rank and then specify how many of the records to return so in this case 10. Have a search in google and you will find some good examples of how to use it.
Amy
shankar vembu
Ranch Hand

Joined: May 10, 2001
Posts: 309
Originally posted by David Peterson:
You could use a SELECT statement with an ORDER BY clause and then pull off just the first ten rows using a for loop.

I would prefer to do this, other alternatives are database dependent.
Regards.
faisal khan
Ranch Hand

Joined: Mar 13, 2002
Posts: 47
thanks .....
Raj Ma
Greenhorn

Joined: Mar 22, 2004
Posts: 1
you try this..
SELECT * FROM
(SELECT STUDENT.*, DENSE_RANK() OVER(ORDER BY MARKS DESC) AS RK
FROM STUDENT)
WHERE RK <=5
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: SQL query for top ten students