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

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.


How to Limit Sql Query result in MS SQL, My SQL, Postgres, Oracle Database? Reviewed by Ravi Kumar on 12:55 PM Rating: 5

2 comments:

All Rights Reserved by Etechpulse © 2012 - 2017

Contact Form

Name

Email *

Message *

Powered by Blogger.