Find the Nth Highest record in Oracle.
=============================

Lets consider the table Student
SQL> create table student
(
SNO  NUMBER,
SNAME  VARCHAR2(20),
MARKS NUMBER
);
Lets insert some records into that DB
SQL>insert into student values(23,’kumar’,437);
SQL>insert into student values(35,’kumar’,200);
SQL>insert into student values(41,’sankar’,200);
SQL>insert into student values(49,’raja’,209);
SQL>insert into student values(10,’ramesh’,219);
SQL>commit;
Getting Student details in ascending order based on sno

SQL> select * from student order by sno;

Getting student with 1st sno


SQL> select * from student where sno=(select min(sno) from student);

Getting Student who has got Highest Mark


SQL> select * from student where marks=(select max(marks) from student);

This query can give you only highest mark,But not the 2nd highest mark,3rd highest mark.Which ever the user are intrested to see.

To do that,we can take the support of rownum concept

Get the Student with Highest Mark

SQL> select * from student where marks=(select max(marks) from student where rownum<=1);

Output:-same as above

To get the Student details who has 2nd highest mark only change rownum<=2,

For 3rd highest mark rownum<=3 and so on….

Assignment

WAP to delete nth student from the database,where nth is his position according to sno

DeleteSecondHighestStudent.java


import java.sql.*;
import java.util.Scanner;
class DeleteTest
{
public static void main(String[] args)throws Exception
{
Scanner sc=new Scanner(System.in);
System.out.println("enter record number which you want to delete");
int stno=sc.nextInt();

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","manager");

String qry="delete  from student where sno=(select max(sno) from student where rownum<=?)";
//create PreparedStatement object
PreparedStatement pst=con.prepareStatement(qry);
pst.setInt(1,stno);//set value to parameter

//execute the sql query
int count=pst.executeUpdate();

if(count!=0) System.out.println(count+" Record deleted successfully");
else            System.out.println("Record deletion failed");

pst.close();
con.close();
}
}

                                                         Before Execution

c:\>javac DeleteTest.java

c:\>java DeleteTest
enter record number which you want to delete: 2
1 Record deleted successfully

                                                                After Execution

We can see,2nd record got deleted.
Note:dont forget to add ojdbc14.jar in the classpath