How Can I set result set row count using sql query

Question

I wanted to set the number rows returned per query say 5. How can I set this in the sql query. Can any one please help me

Answer 1

Highly dependent on what RDBMS you are using.

For Oracle

 SELECT * FROM the_table WHERE ROWNUM < 6

(since 12c there is another option, too).

For Postgresql

 SELECT * FROM the_table LIMIT 5

Answer 2

According to the MySQL manual you can do this by adding LIMIT statement to your query:

SELECT * FROM tbl_name
LIMIT offset, row_numbers

or

SELECT * FROM tbl_name
LIMIT row_numbers OFFSET offset

offset option is very useful in case of pagination.

Answer 3

SELECT TOP 5 *
FROM dbo.MyTable

Answer 4

As someone suggest zou can use:

select top X from table_name

where X is the numer of rows that you want

or you can use row_number

With cte AS 
( SELECT *, 
ROW_NUMBER() OVER (order by table_column) as RowNumber  
FROM table_name) 
select * 
from cte
Where RowNumber <= 5

or even:

With cte AS 
( SELECT *, 
ROW_NUMBER() OVER (order by table_column) as RowNumber  
FROM table_name) 
select * 
from cte
Where RowNumber between 5 and 10