How can i select rows from 500 to 1000 using the select statement?
Asked
Active
Viewed 3,603 times
0
-
Access Database and i am using vb.net – user1570048 Aug 09 '12 at 16:47
-
@user1570048 does your table have a numeric ID field that would correspond to 500-1000? – Taryn Aug 09 '12 at 16:50
-
What determines which row is row 500? How are the data ordered? – mikeY Aug 09 '12 at 16:51
-
When you say rows 500 to 1000, what's your criteria? Sql rows are not ordered by anything and thus their order could be different on different select statements. Unless you have a specific id or date-stamps this question makes no sense. – Florin Stingaciu Aug 09 '12 at 16:51
-
2check this solution - http://stackoverflow.com/questions/1701243/returning-row-number-on-ms-access, you can then do `select * from tbl where rownumber between 500 and 1000` – rs. Aug 09 '12 at 16:52
-
no it doesn't have numerical value if it did i wouldnt ask – user1570048 Aug 09 '12 at 16:55
-
its like when you say "Select TOP 500 * ..." – user1570048 Aug 09 '12 at 16:57
-
possible duplicate of [MS Access LIMIT X, Y](http://stackoverflow.com/questions/8627032/ms-access-limit-x-y) – HansUp Aug 09 '12 at 17:43
2 Answers
2
In most databases, you would use something like row_number() over (order by col1, col2) to assign a row number to a specific ordering. Then you can request rows x through y. See here.
Since MS-Access doesn't have analytic functions like row_number(), you have to be a bit more creative. Here's a KB article that might help you.
dcp
- 54,410
- 22
- 144
- 164
2
Without more info, I will suggest selecting the top 1000, then selecting the bottom 500 from that.
Edit: Record order is by field called name. Perhaps something like:
select top 500 name
from
(
select top 24000 name
from table
order by name
)
order by name desc
Please note: Name is a reserved word and a bad choice for a field name.
mikeY
- 519
- 4
- 14
-
-
@user1570048 I have no idea what you are talking about. 2000? There are brilliant people here. If you provide more information I'm sure you will have an answer in minutes. – mikeY Aug 09 '12 at 17:07
-
i have like 36000 records for example i want to be able to select between 23500 and 24000 and so on :) – user1570048 Aug 09 '12 at 17:10
-
1@user1570048 You have been asked a number of times about how the records are ordered. Just exactly how is record 23,500 determined? If you can provide that, I'm sure an answer would be forthcoming. – mikeY Aug 09 '12 at 17:12
-