Limit number of rows 1

[aspx]

Hi!
I'm building Web-application and I want to limit the number of displayed records (1-10, 11-20, 21-30 etc. as hyperlinks on the page). If I use MySQLSever - there is built-in function LIMIT(start,record_numbers). But unfortunately I couldn't find equivalent function in MSSQLServer. MSSQL's finction TOP still returns too much records. I need to display only 10 records per time, so it's useless to get all recordset.
Do you have any ideas (and examples)?
TIA.

 

[royjimenez]

you should limit your retrieve arguments, 'cause u shouldn't revtrieve many rows.
But if u are using ASP u can keep your rows into a resultset and y can get the rows
like u want.

Regards

 

[aspx]

Unfortunately I'm using PHP. I can manipulate the resultset by PHP, but every time the page is requested and query is executed, SQLServer will return me all resultset.
Imagine 700 records in the recordset - I need only 10 records - for example records 290 to 300 - it's a waste of network traffic. So how is possible to retrieve only records 290 till 300 of the returned resultset?
I've read about cursors in MSSQL, but I'm not sure if it will solve the problem...

 

[JamesLean]

You can use syntax something like this:

SELECT TOP 10 col1, col2
FROM tbl
WHERE col1 NOT IN (
  SELECT TOP 10 col1
  FROM tbl
  ORDER BY col1
)
ORDER BY col1

That will get you records 11-20. The first TOP statement is how many records you want to return and the second TOP (in the subquery) sets where you want to start from.

--James

 

[aspx]

Excellent, 10x, James!
It works fine.
But I wonder for some optimization.
I wrote stored procedure which takes 2 arguments @num1(number of displayed records), @num2(position):
CREATE stored procedure stTest
@num1 int, @num2 int
AS
SELECT TOP @num1 col1, col2
FROM tbl
WHERE col1 NOT IN (
SELECT TOP @num2 col1
FROM tbl
ORDER BY col1
)
ORDER BY col1
But MSSQL didn't accept this SQLStatement, probably because T-SQL expect parameters only for WHERE-clause...
Is there any solution of that problem?
TIA.

 

[JamesLean]

You'll need to use dynamic sql for this:

DECLARE @sql varchar(1000)

SET @sql = 'SELECT TOP ' + CAST(@num1 AS varchar) + ' col1, col2
FROM tbl
WHERE col1 NOT IN (
  SELECT TOP ' + CAST(@num2 AS varchar) + ' col1
  FROM tbl
  ORDER BY col1
)
ORDER BY col1'

EXEC(@sql)

--James

 

[aspx]

10x again , James!
2 stars!!!
Extremely useful for me!