sql top limit

By | January 9, 2020

TOP, LIMIT, ROWNUM in SQL:

If you want to fetch top N rows from table, you can use TOP clause. This clause is useful when a table contains thousands of records because returning so many records can impact the performance.

But all databases may not support TOP clause.

SQL Server – TOP
MySQL supports LIMIT clause to select limited number of rows.
Oracle server supports ROWNUM

SQL Server/MS Access:
select TOP 5 * from users;

MySQL:
select * from users LIMIT 5;

Oracle server:
select * from users where ROWNUM<=5;

SQL TOP PERCENT:
Below sql statement fetches the first 25% of records from users table.
select TOP 25 PERCENT * from users;

WHERE clause in TOP/LIMIT/ROWNUM:
select TOP 5 * from users where state=’california’;
select * from users where state=’california’ LIMIT 5;
select * from users where where state=’california’ AND ROWNUM<=5;

 

Must reads:

SQL Date Functions

SQL quick reference for interview preparation

Java program to split string

Maps in Java

Java interview questions

Hashset and LinkedHashset differences in java

common exceptions in Selenium Webdriver

Leave a Reply

Your email address will not be published. Required fields are marked *