Latest

random

How to Limit Sql Query result in MS SQL, My SQL, Postgres, Oracle Database?

SQL SELECT TOP, LIMIT, ROWNUM - In a Query



Recently in one of the interviews I was asked how to Limit SQL Query result in MS SQL, My SQL, Postgres, Oracle Database. For example I have a table named employee in my database so find the query example below in different databases :-

how to limit,query result,limit query,sql tutorial,sql queries,select for sql,in sql,in MS SQL, My SQL, Postgres, Oracle Database

In MS SQL (Using top)

Example 1: Returning first 50 rows from a table:
select top 50 * from employee

Example 2: Returning top 10% of rows from a table
select top 10 percent * from employee




In MySQL (Using limit)

Example 1: Returning the first 100 rows from a table
select * from employee limit 100

Example 2: Returning a range of rows from a table (starting at record 2, return the next 4 rows)
select * from employee limit 2,4



In PostGres (Using limit)

Example 1: Returning the first 100 rows from a table
select * from employee limit 100

Example 2: Returning 10 records from the table employee starting at row 3
select * from employee limit 10 offset 3


 
In Oracle (Using rownum)

Example 1: Returning the first 100 rows from a table
select * from employee where rownum <= 100

Example 2: Returning the first 100 rows in order of salary from a table
select * from (select * from employee order by salary desc) where rownum <= 100

Note: that the rownum query has to be wrapped around an inner select that actually does the order by.


2 comments:

All Rights Reserved by Etechpulse © 2015 - 2016
Powered By Blogger

Contact Form

Name

Email *

Message *

Powered by Blogger.