Oracle's equivalent of SQL Server's TOP command

Rownum is used in Oracle to retrieve the N-top records.

SELECT * FROM emp  WHERE rownum < 2

Note: Some things to keep in mind are that the “>=” or the “>” operators wont work with ROWNUM. Also remember ROWNUM is to only pick the required row from a result set and should not be confused with the position of the record in the table.

you want the first row of an ordered result set, you need to do the following:

SELECT e.*   FROM (SELECT * FROM emp ORDER BY empno) e WHERE rownum < 2 ;

The reason why is because rownum is assigned to the result set as the records are retrieved but before they are sorted. For that reason you must order the result set in an in-line view before using rownum to retrieve the first row (or any number of first rows for that matter).

Do some R & D in Row_Number() in ORACLE

Sql Server Performance Tuning

Sql Server Performance Tuning

 

Sql Server stored procedure performance tuning
http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx

 

Stored procedure performance optimization
http://bytes.com/topic/sql-server/answers/83488-stored-procedure-performance-optimization

 

SQL Server 2005: SQL Query Tuning (By Microsoft Tech Ed)
http://download.microsoft.com/documents/australia/teched2005/hol/HOL076.pdf

Other sites
http://www.mssqltips.com/category.asp?catid=9

2nd Highest Salary

Method 1

Correlated sub queries are more generalized to find n'th highest or nth lowest salary.


To find nth highest salary

SELECT * FROM Employee E1 WHERE n-1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd lowest salary

SELECT * FROM Employee E1 WHERE 1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

To find nth lowest salary

SELECT * FROM Employee E1 WHERE n-1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

Method 2
 
HIGHEST 2nd Salary :


;WITH CTEs

AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) 'Rank',* FROM EMP)

SELECT Names,Salary FROM CTEs WHERE Rank=2

LOWESE 3Rd Salary :


;WITH CTEs

AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY) 'Rank',* FROM EMP)

SELECT Names,Salary FROM CTEs WHERE Rank=3

Note: Desc is removed.