MySQL Selecting from x to y records

I tried this with SQL Server 2000 and it gets a compilation error. Do you or anyone know of a way to do this with SQL Server? Right now if I want records 20-50 I select Top 50 records into a datatable and then delete the first 19.

Thanks,
OnTheAnvil
 
This might be a way to do what you need:
Code: 
DECLARE @RangeStart int
SET @RangeStart = 20

DECLARE @StartPoint int

SET @StartPoint = (SELECT MAX([PrimaryKey])
FROM (SELECT TOP 20 [PrimaryKey] FROM [MyTable]) T)

SELECT TOP 10 * FROM [MyTable] WHERE [PrimaryKey] > @StartPoint
 
Thanks for the response. I had hoped for a simpler solution but I guess programming is never simple. This does assume that your [PrimaryKey] is incremental correct?

~OnTheAnvil

Mister E said:
This might be a way to do what you need:
Code: 
DECLARE @RangeStart int
SET @RangeStart = 20

DECLARE @StartPoint int

SET @StartPoint = (SELECT MAX([PrimaryKey])
FROM (SELECT TOP 20 [PrimaryKey] FROM [MyTable]) T)

SELECT TOP 10 * FROM [MyTable] WHERE [PrimaryKey] > @StartPoint
Click to expand...
 
Yes, the [PrimaryKey] field would be an auto-incrementing integer IDENTITY field. You could easily adjust the numbers to fit your desired range.
 
EFileTahi-A said:
Imagine that I have a table with 100 records and that I need to select all records from position 20 to 50...

How?

Thank you...
Click to expand...
you may using Between or In method in sql query,
SELECT * FROM Table_name WHERE Code BETWEEN "20" AND "50"
or
SELECT * FROM Table_name WHERE Code IN "20" TO "50"
 
bshaen said:
you may using Between or In method in sql query,
SELECT * FROM Table_name WHERE Code BETWEEN "20" AND "50"
or
SELECT * FROM Table_name WHERE Code IN "20" TO "50"
Click to expand...
Well that assumes that "Code" is completely sequential, which is not guarenteed. If there are any gaps in "Code" between 20 and 50, then youre not going to get a range of 30 records.
 
Back
Top