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
-
2Which DBMS ? MySQL ? MSSQL ? Oracle ?Raptor– Raptor2014-06-06 07:51:07 +00:00Commented Jun 6, 2014 at 7:51
Add a comment
|
4 Answers
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
1 Comment
John Smith
And Also I want this for pagination
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.
Comments
SELECT TOP 5 *
FROM dbo.MyTable
Comments
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
1 Comment
sdrzymala
In tha case when we are talking about MSSQL