CODEWITHSUNDEEP
mysqlsql
John Smith
John Smith

Reputation: 11

How Can I set result set row count using sql query

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

Upvotes: 0

Views: 3597

Answers (4)

bronislav
bronislav

Reputation: 782

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.

Upvotes: 1

sdrzymala
sdrzymala

Reputation: 387

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

Upvotes: 0

Thilo
Thilo

Reputation: 262534

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

Upvotes: 1

BI Dude
BI Dude

Reputation: 2016

SELECT TOP 5 *
FROM dbo.MyTable

Upvotes: 0

Related Questions