CODEWITHSUNDEEP
sqlcountrangebetween
Adam James
Adam James

Reputation: 4191

SQL results by row count range

I am looking for a way to return the rows of a table between a range, for instance how would I get rows 100 to 200 in a table that has 10,000 rows?

This is not based off of any of the data within the row, and the primary key although incremental, cannot be used as an index.

I am working with a rather large table that has to be shown to the user in an application. Right now I am getting all of the records and using a Java Array to get the rows x to y to send back to the user via Ajax (web application).

This method takes quite a bit more time than I would like (though less than sending all of the records to the user at once) so I would just like to query the records I need.

Any help is appreciated, thank you!

Upvotes: 1

Views: 624

Answers (3)

Bohemian
Bohemian

Reputation: 425198

You can use LIMIT, but you must order the rows too.

select *
from table
order by id -- very important
limit 100,100

If you don't order the rows, theoretically you could always get the same 100 rows when changing the starting row. This is because unordered queries can return the rows in any order, and it will vary depending on other queries running at the same time and what rows are cached in memory, etc.

Upvotes: 0

Deepak Mishra
Deepak Mishra

Reputation: 3183

something like this may help you....

SELECT PriceRank, ProductName, UnitPrice
    FROM
       (SELECT ProductName, UnitPrice,
           ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
        FROM Products
       ) AS ProductsWithRowNumber
    WHERE PriceRank > <i>StartRowIndex</i> AND
        PriceRank <= (<i>StartRowIndex</i> + <i>MaximumRows</i>)

http://msdn.microsoft.com/en-us/library/bb445504.aspx

Upvotes: 0

Aviator64
Aviator64

Reputation: 56

select * from table limit 100,100

http://dev.mysql.com/doc/refman/5.5/en/select.html and search for "limit"

Upvotes: 3

Related Questions