MySQL Selecting from x to y records

[EFileTahi-A]

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...

[EFileTahi-A]

I discovered the method...

 

"SELECT * FROM tablename Limit x,y" :D

 

- THREAD RESOLVED -

Edited March 4, 2005 by EFileTahi-A

[OnTheAnvil]

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

[Mister E]

This might be a way to do what you need:

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

[OnTheAnvil]

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

 

This might be a way to do what you need:

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

[Mister E]

Yes, the [PrimaryKey] field would be an auto-incrementing integer IDENTITY field. You could easily adjust the numbers to fit your desired range.

[bshaen]

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...

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"

[Mister E]

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"

Well that assumes that "Code" is completely sequential, which is not guarenteed. If there are any gaps in "Code" between 20 and 50, then you're not going to get a range of 30 records.