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
=============================
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