Returning range of records in MSSQL

Hi guys, I need to know if there is a way to select a range of records from a database. Kind of like using SELECT TOP 1000, but I need to be able to specify which records to return. So I imagine it would look like this:

SELECT TOP 2000-5000 * FROM customers WHERE groupid=2 ORDER BY FirstName DESC

Where this statement would return only records 2000 to 5000 of the returned results.Did you try reading the manual?

Not found it?

Reason: no, there is no way to do this.

Sorry.|||Well, you *could* do something like this, although I suggest you check the performance.

SELECT
*
FROM
(
SELECT TOP 3001
*
FROM
(
SELECT TOP 5000
*
FROM
customers
WHERE
groupid = 2
ORDER BY
FirstName DESC
) AS S1
ORDER BY
FirstName ASC
) AS S2
ORDER BY
FirstName DESC

Terri

Returning Range of Records from SQL Server 7 - Old issue revisited

Hi all,

I am using the following stored procedure in SQL 7 to return a range of records.

CREATE PROCEDURE spRetMyTable
@.maxRows int, @.lastRecord int, @.SortPhrase varchar(122)
AS
begin
DECLARE @.stSql VARCHAR(255)
SET ROWCOUNT @.maxRows
SET @.stSql = 'SELECT * FROM (SELECT TOP 10 colPrimary, col1, col2, col3 FROM (SELECT TOP ' + CONVERT(VARCHAR, @.lastRecord) +
'colPrimary, col1, col2, col3 FROM [MyTable] ORDER BY [colPrimary] ASC) AS tbl1 ORDER BY [colPrimary] DESC) AS tbl2 ORDER by [colPrimary] ASC'
exec(@.stSql)
set rowcount 0
end

To execute the above procedure I am issuing the command:

execute spRetMyTable @.maxRows 10, @.lastRecord 1500

The column [colPrimary] is the primary field in the table.

I will be using the selected records in a gridView with paging (3 records per page). So if the next page is selected I would require to take out record 1491 to record 1494 and Add record 1501 to record 1503 in the datatable subject to the availability of records in the table MyTable.

How do I provide sorting by col1, col2 or col3? It could be single column or multiple column sorting as chosen by the user during runtime.

Is there any performance overhead in the above method in the first place?
Could it be improved by any alternative process?

I need YOUR ASSISTANCE to cater to the above issues in a feasible way.

Thanks in advance.

You may take a look atSorting Data with Data Source Controls

|||

HiIori_Jay,

Thanks. I will try on the advised line.