Friday, February 25, 2011

How to get top n Record in Oracle like Sql



Sample Query to get Top n Record from oracle

Select * from tableName where FiledName =”” and rownum<=5

The above statement will give u top 5 record
rownum<=5

A few more queries -

Not in

SELECT 'TRUE' 
    FROM emp 

    WHERE deptno NOT IN (5,15); 


LIKE Operator

SELECT sal 
    FROM emp 

    WHERE ename LIKE 'SM%';

Case Sensitivity and Pattern Matching

UPPER(ename) LIKE 'SM%'

Oracle UNION Example 

SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_null(null), date_in
    FROM orders_list2;

Oracle UNION ALL Example 
Result - SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

MINUS Example

SELECT part 
    FROM orders_list1 
MINUS 
SELECT part 
    FROM orders_list2;

INTERSECT  Example
SELECT part 
    FROM orders_list1 
INTERSECT 
SELECT part 

    FROM orders_list2;



No comments:

Post a Comment