Different Ways of Fetching Top N records in Sql-Server,Oracle,Mysql,DB2 and PostgreSql Databases

vishalneeraj-24503
Posted by vishalneeraj-24503 under Others category on | Points: 40 | Views : 1121
It's very easy to fetch Top N records from DB but in a different ways.

For ex:-

1). SQL-Server-> Top clause is used for fetching N records in ascending or descending order.

For Ex:-

Select Top 20 Employee_id,Employee_name,Employee_code,address from employee_master where status = 'AA';


2). Oracle -> RowNum clause is used.

For Ex:-

Select Employee_id,Employee_name,Employee_code,address from employee_master where status = 'AA' and RowNum <= 20;


3). MySql -> Limit clause is used.

For Ex:-

Select Employee_id,Employee_name,Employee_code,address from employee_master where status = 'AA' order by Employee_name,Employee_code asc Limit 20;


4). DB2 -> Fetch First N Rows Only is used.

For Ex:-

Select Employee_id,Employee_name,Employee_code,address from employee_master where status = 'AA' order by Employee_name,Employee_code asc 
Fetch First 20 Rows Only;


5). PostgreSql DB -> Limit as well as Offset clause is used.

For Ex:-

Select Employee_id,Employee_name,Employee_code,address from employee_master where status = 'AA' order by Employee_name,Employee_code asc
Limit 10 Offset 20;


Note:- In Offset case,it will skip the row from 1 to 19 and return the record from 20th to 30th record.

Comments or Responses

Login to post response